進行SI 時, 總會遇到比較經典的File base 整合案例, Excel 便是其中一例. 在此示範如何存取Excel 檔案.在此建立了一個ExcelHelper 進行Excel 相關的存取動作.
ExcelHelper.cs
public class ExcelHelper { public DataTable readExcelToDataTable(string filePath, int sheetNum, int headerLine, int contentLine) { this.loadExcelFile(filePath); this.resetCurrentDataTable(); this.readExcelHeader(sheetNum, headerLine, contentLine); return this.readExcelData(sheetNum, contentLine); //line 0 is header, data start from line 1 } public void loadExcelFile(string filePath) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { currentExcelFileType = Path.GetExtension(filePath); if (currentExcelFileType == ExcelFileType.xls) { currentWorkbook = new HSSFWorkbook(file); } else if (currentExcelFileType == ExcelFileType.xlsx) { currentWorkbook = new XSSFWorkbook(file); } } } public void resetCurrentDataTable() { currentTable = new DataTable(); } public void readExcelHeader(int sheetNum, int headerline, int contentline) { ISheet sheet = currentWorkbook.GetSheetAt(sheetNum); IRow row = sheet.GetRow(headerline); IRow contentRow = sheet.GetRow(contentline); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); ICell contentCell = contentRow.GetCell(i); // Handle incorrect format import if 1st content row has empty data. while (contentCell.IsCellEmpty()) { contentRow = sheet.GetRow(contentline++); contentCell = contentRow.GetCell(i); } // Base on content value to decide Data coulumn type. if ((cell != null) && (contentCell != null)) { // ToDo: Refactor repeatable code. if (contentCell.CellType == CellType.Numeric) { // Handle date time format. if (HSSFDateUtil.IsCellDateFormatted(contentCell)) this.addImportTableColumn(cell.StringCellValue, TableColumnType.DateTime); else this.addImportTableColumn(cell.StringCellValue, TableColumnType.Decimal); } else if (contentCell.CellType == CellType.Formula) { switch (contentCell.CachedFormulaResultType) { case CellType.String: this.addImportTableColumn(cell.StringCellValue, TableColumnType.String); break; case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(contentCell)) this.addImportTableColumn(cell.StringCellValue, TableColumnType.DateTime); else this.addImportTableColumn(cell.StringCellValue, TableColumnType.Decimal); break; } } else if (contentCell.CellType == CellType.String) { this.addImportTableColumn(cell.StringCellValue, TableColumnType.String); } else { this.addImportTableColumn(cell.StringCellValue, TableColumnType.String); } } } } public DataTable readExcelData(int sheetNum, int offsetLineNum) { ISheet sheet = currentWorkbook.GetSheetAt(sheetNum); int numOfRows = sheet.LastRowNum; for (int r = offsetLineNum; r < numOfRows - 1; r++) { IRow row = sheet.GetRow(r); DataRow dr = currentTable.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { if ((cell.CellType == CellType.Numeric) && (currentTable.Columns[i].DataType.IsNumericType())) { if (HSSFDateUtil.IsCellDateFormatted(cell)) dr[i] = cell.DateCellValue; else dr[i] = cell.NumericCellValue; } else if (cell.CellType == CellType.Formula) { switch (cell.CachedFormulaResultType) { case CellType.String: dr[i] = cell.StringCellValue; break; case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell)) dr[i] = cell.DateCellValue; else dr[i] = cell.NumericCellValue; break; } } else if (cell.CellType == CellType.String) { dr[i] = cell.StringCellValue; } // Create default empty value. else if (cell.IsCellEmpty()) { dr[i] = Activator.CreateInstance(currentTable.Columns[i].DataType); } else { dr[i] = cell.ToString(); } } } currentTable.Rows.Add(dr); } return currentTable; } }
Leave a Reply