/// <summary>
/// Đọc file excel ra datatable
/// </summary>
/// <param name="strPath">Đường dẫn đến file</param>
/// <param name="isDelete">Có xóa file sau khi đọc</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(string strPath, bool isDelete = false, int curCol = 0, int minRow = 0, int sheetIndex = 0)
{
IWorkbook wb = ReadExcelToIWorkBook(strPath, isDelete);
if (wb == null)
{
return(null);
}
ISheet sheet = wb.GetSheetAt(sheetIndex);
DataTable dt = new DataTable();
IRow headerRow = sheet.GetCreateRow(0);
IEnumerator rows = sheet.GetRowEnumerator();
int colCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
//Neu so cột không đúng thì ko đọc tiếp
if (curCol > 0 && curCol != colCount)
{
return(dt);
}
//Nếu số dong không đúng thì ko đọc tiếp
if (minRow > 0 && rowCount < minRow)
{
return(dt);
}
//add Columns
for (int c = 0; c < colCount; c++)
{
dt.Columns.Add(headerRow.GetCell(c).ToString());
}
bool hashValue = false;
DataRow dr = dt.NewRow();
rows.MoveNext();//Bo row dau
IRow row;
//Add row
while (rows.MoveNext())
{
row = (IRow)rows.Current;
if (hashValue)
{
dr = dt.NewRow();
}
hashValue = false;
for (int i = 0; i < colCount; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
CellType _cellType = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;
switch (_cellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
dr[i] = cell.DateCellValue.ToString(Enums.FormatType.FormatDateVN);
}
else
{
dr[i] = cell.NumericCellValue.ToString();
}
hashValue = true;
break;
case CellType.String:
dr[i] = cell.StringCellValue;
hashValue = true;
break;
}
}
}
if (hashValue)
{
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>
/// Đọc excel và ghi dữ liệu IWorkBook Chưa hoàn thành
/// </summary>
/// <param name="strPath">Đường dẫn đến file excel</param>
/// <param name="dtSource"></param>
/// <param name="rowStart"></param>
/// <param name="colStart"></param>
/// <param name="IsCount">True - Có STT, False - Không có STT</param>
/// <returns></returns>
/// <modified>
/// Author Date comment
/// anhhn 15/07/2012 Tạo mới
/// </modified>
public static IWorkbook WriteExcelByTemp(this DataTable dtSource, string strPath, int rowStart, int colStart, bool IsCount = false)
{
IWorkbook wb = ReadExcelToIWorkBook(strPath);
if (wb == null)
{
return(wb);
}
int rowCount = dtSource.Rows.Count;
if (rowCount == 0)
{
return(wb);
}
int colSTT = 0;
if (IsCount)
{
colSTT++;
}
int colCount = dtSource.Columns.Count;
ISheet sheet = wb.GetSheetAt(0);
int indexRow = rowStart;
IRow Row = sheet.GetRow(indexRow++);
if (Row.Cells.Count < colCount + colSTT)
{
return(wb);
}
ICellStyle[] lstCellStyle = new ICellStyle[colCount + colSTT];
ICell cell;
int indexCol = colStart;
DataRow dr = dtSource.Rows[0];
if (IsCount)
{
cell = Row.GetCell(indexCol);
lstCellStyle[indexCol - colStart] = cell.CellStyle;
cell.SetCellValue(1);
indexCol++;
}
for (int i = 0; i < colCount; i++)
{
cell = Row.GetCell(indexCol);
lstCellStyle[indexCol - colStart] = cell.CellStyle;
//UNDONE Lấy kiểu của dữ liệu
cell.SetCellValue(dr[i].ToString());
indexCol++;
}
for (int i = 1; i < dtSource.Rows.Count; i++)
{
Row = sheet.GetCreateRow(indexRow++);
indexCol = 0;
dr = dtSource.Rows[i];
indexCol = colStart;
if (IsCount)
{
cell = Row.CreateCell(indexCol);
cell.SetCellValue(i + 1);
cell.CellStyle = lstCellStyle[indexCol - colStart];
indexCol++;
}
for (int j = 0; j < colCount; j++)
{
cell = Row.CreateCell(indexCol);
cell.SetCellValue(dr[j].ToString());
cell.CellStyle = lstCellStyle[indexCol - colStart];
indexCol++;
}
}
return(wb);
}