Msaada: .NET Excel Programming

Wataalamu wa software nina issue hapa, leteni mawazo yenu

Use case:
Nina web(with ftp) server ambayo inapokea (excel)files kibao kila siku,kutoka kwa client mbalimbali.
Ftp server inapokea na kuhifadhi katika ftproot directory.
Meanwhile web server inatakiwa ku respond kwa ku extract data kutoka kwenye file moja baada ya lingine na kuzihifadhi kwenye (relational)database.

Challenge:
>Timing
Nitumie approach gani ambayo itaiwezesha web server application ku react pale tuu file linapoingia kwenye ftp server?

> Data extraction
Excel files zinazokuja hazina pre defined format, ila zina columns fulani ambazo cells data zake ndiyo zitakuwa extracted.
Mfano:-
File la kwanza linawezakuja na column k.v UserID,FirstName,LastName.
File lingine linawezakuja na UserID, Amount, PaymentDate, ApprovedBy, ApprovedDate.
File lingine linawezakuja na UserID, ServiceOffered, ServiceDescription, Company.

.NET library ipi na approach ipi itafaa zaidi kulingana na context na content structure ya haya ma file?

Progress:
>Timing
Tunafanya ku-poll ftp server kila baada ya dakika 15 hivi. Hii threshold siyo nzuri kwani inaweza kula server resources hata kama hakuna file lilokuwa received.

>Data extraction
Nimetumia .NET System.Data.OleDb ku manipulate excel file, ingawa extraction logic haijakaa vizuri sana, siyo ad-hoc oriented kivile.

Naomba idea zenu katika ku approach hii issue.

Pamoja

Nadhani upo sawa kwenye polling hakuna jingine hapo, haiwezi kuchukua resources nyingi simply kuangalia kama kuna files mpya, kama kuna mpya unazicopy kuziprocess kisha unazimove katika folder nyingine kuwa zishafanyiwa kazi.

Kuna FileSystemWatcher Class FileSystemWatcher Class (System.IO) katika .Net ambayo kazi yake ni kumonitor folder for new/changed files sijui unaandika program ya aina gani kwenye .Net ila unaweza kuitumia hiyo hasa kwenye Windows Service application.

Issue ya pili inabidi uwe na njia ya kuidentify file ipi ina format ipi filename ni njia mojawapo so mafile yenye jina linaishia na _Users.xlsx yatafuata format fulani so tumia method hii kuzisoma data.

Kama kuna column names unaweza kujaribu kusoma column name kwanza kisha kama ndo unayoihitaji ukazoma data zake.

Pia kama format inatofautiana kati ya wateja unaweza ukawapa kila mmoja folder yake kwenye FTP then kila mmoja awe ana logic yake kwenye code.

Kama format zinabadilika kila wakati hapo unajitengenezea matatizo makubwa in the end, ni muhimu kwenye software interfaces kati ya systems tofauti ziwe well defined na hazibadiliki ovyo.

Kutegemea na uhahijati kusoma excel files za mwaka gani pia library unazoweza kutumia zinabadilika e.g. GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files

Kang said:

Nadhani upo sawa kwenye polling hakuna jingine hapo, haiwezi kuchukua resources nyingi simply kuangalia kama kuna files mpya, kama kuna mpya unazicopy kuziprocess kisha unazimove katika folder nyingine kuwa zishafanyiwa kazi.

Kuna FileSystemWatcher Class FileSystemWatcher Class (System.IO) katika .Net ambayo kazi yake ni kumonitor folder for new/changed files sijui unaandika program ya aina gani kwenye .Net ila unaweza kuitumia hiyo hasa kwenye Windows Service application.

Issue ya pili inabidi uwe na njia ya kuidentify file ipi ina format ipi filename ni njia mojawapo so mafile yenye jina linaishia na _Users.xlsx yatafuata format fulani so tumia method hii kuzisoma data.

Kama kuna column names unaweza kujaribu kusoma column name kwanza kisha kama ndo unayoihitaji ukazoma data zake.

Pia kama format inatofautiana kati ya wateja unaweza ukawapa kila mmoja folder yake kwenye FTP then kila mmoja awe ana logic yake kwenye code.

Kama format zinabadilika kila wakati hapo unajitengenezea matatizo makubwa in the end, ni muhimu kwenye software interfaces kati ya systems tofauti ziwe well defined na hazibadiliki ovyo.

Kutegemea na uhahijati kusoma excel files za mwaka gani pia library unazoweza kutumia zinabadilika e.g. GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files

Click to expand...

Yeah, nimecheki integration ya FileSystemWatcher with ASP.NET naona iko poa. Ngoja niifanyie kazi.


Data extraction logic iko hivi:
1. Get excel file
2. Read column
3.What to extract?
if(HasFirstname){
> hii method inacheki kama excel file lina column yenye first name
> then ina extract details za user k.v lastname,userid, n.k
}
else if(HasAmountPaid){
> hii inacheki kama excel lina column ya amount paid
> then lina extract details za malipo
}else if(HasServiceOffered){
>hii inacheki service iliyokuwa assigned to this user
> then ina extract details za huduma hiyo
}

So far nitakuwa kwa mfano huu hapa juu, unaweza ku-handle aina tatu za excel files zilizotumwa to ftp server.
Changamoto niliyoiona hapa ni pale unakuta exception kama vile ...Excel hii haina column ya "Firstname", wakati null check logic nimeweka vizuri kabisa. Kwa maana ya kwamba ikikosa hiyo "Firstname" column katika excel hilo basi icheki, kama "AmountPaid" column ipo, kama haipo icheki,....


Kwa ufupi sidhani kama hii logic ni nzuri. Naomba wataalamu mnipe mwongozo.

Thanks kwa mchango wako mkuu.