Contoh kode menggunakan lib NPOI untuk membaca file excel (xlsx)
[code language=”csharp”]
//kamus
List<ManufacturerFormStub> ret = new List<ManufacturerFormStub>();
string imagePath = null;
//algoritma
string currentFilePathInServer = HttpContext.Current.Server.MapPath(FilePath);
XSSFWorkbook book;
using (FileStream file = new FileStream(currentFilePathInServer, FileMode.Open, FileAccess.Read))
{
book = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)book.GetSheet("manufacturer");
for (int row = 1; row < sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
//format as string
for (int col = 0; col <= 8; col++)
sheet.GetRow(row).GetCell(col).SetCellType(CellType.String);
ManufacturerFormStub oneRow = new ManufacturerFormStub();
if (sheet.GetRow(row).GetCell(0).StringCellValue != "")
oneRow.Name = sheet.GetRow(row).GetCell(0).StringCellValue;
if (sheet.GetRow(row).GetCell(1).StringCellValue != "")
oneRow.Address = sheet.GetRow(row).GetCell(1).StringCellValue;
if (sheet.GetRow(row).GetCell(2).StringCellValue != "")
oneRow.CompanyCountry = sheet.GetRow(row).GetCell(2).StringCellValue;
//set company status
CompanyStatusOptions res = CompanyStatusOptions.OTHERS;
bool ok = Enum.TryParse(sheet.GetRow(row).GetCell(3).StringCellValue, out res);
if (ok) oneRow.CompanyStatus = res;
else
{
oneRow.CompanyStatus = CompanyStatusOptions.OTHERS;
}
if (sheet.GetRow(row).GetCell(4).StringCellValue != "")
oneRow.Website = sheet.GetRow(row).GetCell(4).StringCellValue;
if (sheet.GetRow(row).GetCell(5).StringCellValue != "")
oneRow.ProductionCapacity = sheet.GetRow(row).GetCell(5).StringCellValue;
if (sheet.GetRow(row).GetCell(6).StringCellValue != "")
oneRow.ContactName = sheet.GetRow(row).GetCell(6).StringCellValue;
if (sheet.GetRow(row).GetCell(7).StringCellValue != "")
oneRow.ContactPhone = sheet.GetRow(row).GetCell(7).StringCellValue;
if (sheet.GetRow(row).GetCell(8).StringCellValue != "")
oneRow.ContactEmail = sheet.GetRow(row).GetCell(8).StringCellValue;
oneRow.Status = VendorStatus.NEW.ToString();
oneRow.ExpiryDate = null;
if (imagePath != null)
oneRow.Logo = imagePath;
ret.Add(oneRow);
}
}
[/code]
