/// <summary>
/// Get the contents of the format string, by looking up the DataFormat against the supplied workbook
/// </summary>
/// <param name="workbook">The workbook</param>
/// <returns>the format string or "General" if not found</returns>
public String GetDataFormatString(IWorkbook workbook)
{
HSSFDataFormat format = new HSSFDataFormat(((HSSFWorkbook)workbook).Workbook);
int idx = DataFormat;
return(idx == -1 ? "General" : format.GetFormat(DataFormat));
}
/// <summary>
/// 把数字转成日期
/// </summary>
static void SetDate(ISheet ws, HSSFWorkbook workbook, int rowCount, int colCount)
{
//设置单元格格式
NPOI.HSSF.UserModel.HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy/mm/dd hh:mm:ss");
//
IFont fontHeadNull = workbook.CreateFont(); ///
style.Alignment = HorizontalAlignment.Center; //设置单元格的样式：水平对齐居中
style.VerticalAlignment = VerticalAlignment.Top;
fontHeadNull.Color = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
//
style.BorderBottom = BorderStyle.Thin; //边框
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//
for (int i = 0; i < rowCount; i++)
{
IRow rowNow = ws.GetRow(i); //在工作表中：建立行，参数为行号，从0计
for (int j = 0; j < colCount; j++)
{
ICell cellNow = rowNow.GetCell(j); //在行中：建立单元格，参数为列号，从0计
DateTime result;
if (cellNow.CellType != CellType.Numeric)
{
if (DateTime.TryParse(cellNow.StringCellValue.Trim(), out result))
{
cellNow.SetCellValue(Convert.ToDateTime(cellNow.StringCellValue.Trim()));
cellNow.CellStyle = style;
}
}
}
}
}
/// <summary>
/// Get the contents of the format string, by looking up
/// the DataFormat against the supplied workbook
/// </summary>
/// <param name="workbook">The internal workbook.</param>
/// <returns></returns>
public String GetDataFormatString(NPOI.HSSF.Model.InternalWorkbook workbook)
{
HSSFDataFormat format = new HSSFDataFormat(workbook);
return(format.GetFormat(DataFormat));
}
/// <summary>
/// Get the contents of the format string, by looking up
/// the DataFormat against the supplied workbook
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <returns></returns>
public String GetDataFormatString(NPOI.HSSF.Model.Workbook workbook)
{
HSSFDataFormat format = new HSSFDataFormat(workbook);
return format.GetFormat(DataFormat);
}
/// <summary>
/// Get the contents of the format string, by looking up
/// the DataFormat against the bound workbook
/// </summary>
/// <returns></returns>
public String GetDataFormatString()
{
HSSFDataFormat format = new HSSFDataFormat(workbook);
return format.GetFormat(DataFormat);
}
/// <summary>
/// Get the contents of the format string, by looking up
/// the DataFormat against the bound workbook
/// </summary>
/// <returns></returns>
public String GetDataFormatString()
{
HSSFDataFormat format = new HSSFDataFormat(workbook);
return(format.GetFormat(DataFormat));
}
/// <summary>
/// Get the contents of the format string, by looking up the DataFormat against the supplied workbook
/// </summary>
/// <param name="workbook">The workbook</param>
/// <returns>the format string or "General" if not found</returns>
public String GetDataFormatString(IWorkbook workbook)
{
HSSFDataFormat format = new HSSFDataFormat(((HSSFWorkbook)workbook).Workbook);
int idx = DataFormat;
return idx == -1 ? "General" : format.GetFormat(DataFormat);
}
private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName)
{
int columnCount = 0;
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport");
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Set column width
worksheet.SetColumnWidth(0, 40 * 256);
worksheet.SetColumnWidth(2, 20 * 256);
worksheet.SetColumnWidth(5, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 12 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex);
headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export");
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1);
headerRow.CreateCell(0).SetCellValue(peroid);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3);
foreach (DataColumn headercolumn in tmpDataTable.Columns)
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount);
cell.SetCellValue(headercolumn.ColumnName);
if (columnCount == 11)
{
cell.CellStyle = style;
}
columnCount++;
}
// Set value for every row
foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4);
detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString());
detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString());
detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString());
detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString());
detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString());
detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString());
detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString());
detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString());
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.SetCellValue((DateTime)row[FIELD_FROM]);
cell.CellStyle = dateCellStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.SetCellValue((DateTime)row[FIELD_TO]);
cell.CellStyle = dateCellStyle;
detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString());
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
cell.SetCellValue((double)row[FIELD_PERCENTAGE]);
cell.CellStyle = numericStyle;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
/// <summary>
///
/// </summary>
/// <param name="dsTitle">0 第一列为列名，1第二列为显示名,2第三列宽度,3第四列，格式方式</param>
/// <param name="dsData"></param>
/// <returns></returns>
public byte[] tOExcel(DataTable dsTitle, DataTable dsData)
{
try
{
MemoryStream ms = new MemoryStream(); //创建内存流用于写入文件
//System.IO.Stream sr = new System.IO.MemoryStream();
ebook = new HSSFWorkbook();
ISheet esheet = ebook.CreateSheet("sheet1");
//POI操作Excel中，导出的数据不是很大时，则不会有问题，而数据很多或者比较多时，
//就会报以下的错误，是由于cell styles太多create造成，故一般可以把cellstyle设置放到循环外面
//http://blog.csdn.net/johnstrive/article/details/8568113
//设置单元格格式
NPOI.HSSF.UserModel.HSSFCellStyle dstyle = (HSSFCellStyle)ebook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFDataFormat dformat = (HSSFDataFormat)ebook.CreateDataFormat();
dstyle.DataFormat = dformat.GetFormat("yyyy-mm-dd");
//
NPOI.HSSF.UserModel.HSSFCellStyle nstyle = (HSSFCellStyle)ebook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFDataFormat nformat = (HSSFDataFormat)ebook.CreateDataFormat();
nstyle.DataFormat = nformat.GetFormat("#,##0.00");
nstyle.Alignment = HorizontalAlignment.Right;
// ebook.Add(esheet);
if (dsTitle.Rows.Count > 0)
{
IRow erow = esheet.CreateRow(rowi);
int i = 0;
foreach (DataRow row in dsTitle.Rows)
{
ICell ecell = erow.CreateCell(i, CellType.String);
esheet.SetColumnWidth(i, 256 * (row[2] == DBNull.Value ? 80 : Convert.ToInt32(row[2])) / 10);
ecell.SetCellValue(row[1].ToString());
// erow.Cells.Add(ecell);
i++;
}
//加载数据
if (dsData.Rows.Count > 0)
{
foreach (DataRow row in dsData.Rows)
{
//创建新行
rowi++;
IRow derow = esheet.CreateRow(rowi);
//填数据
for (int cj = 0; cj < dsTitle.Rows.Count; cj++)
{
DataRow trow = dsTitle.Rows[cj];
string colname = trow[0].ToString();
CellType vtype = getCellType(dsData.Columns[colname], colname);
ICell ecell = derow.CreateCell(cj, vtype);
switch (vtype)
{
case CellType.Blank:
if (row[colname] != DBNull.Value)
{
if (Convert.ToString(row[colname]) != "")
{
ecell.SetCellValue(Convert.ToDateTime(row[colname].ToString()));
}
}
ecell.CellStyle = dstyle;
break;
case CellType.Numeric:
if (row[colname] != DBNull.Value)
{
ecell.SetCellValue(Convert.ToDouble(row[colname]));
}
ecell.CellStyle = nstyle;
break;
case CellType.String:
ecell.SetCellValue(Convert.ToString(row[colname]));
break;
}
//erow.Cells.Add(ecell);
}
}
}
}
ebook.Write(ms);
long mssize = ms.Length;
byte[] msBuffer = ms.ToArray();
ms.Write(msBuffer, 0, (int)mssize);
ms.Seek(0, SeekOrigin.Begin);
ms.Close();
return(msBuffer);
}
catch (Exception ex)
{
throw ex;
}
//erow.CreateCell(
//return sr;
}
private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(NPOI.HSSF.UserModel.HSSFWorkbook workbook, DataTable dataTable)
{
if (workbook != null)
{
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", ""));
//NPOI.HSSF.UserModel.HSSFRow chineseHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
int columnCount = 0;
//System.Globalization.CultureInfo chineseCI = new System.Globalization.CultureInfo("zh-cht");
foreach (DataColumn headercolumn in dataTable.Columns)
{
headercolumn.ColumnName = headercolumn.ColumnName.Trim();
if (!headercolumn.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
string columnName = headercolumn.ColumnName;
//string chineseColumnName = HROne.Common.WebUtility.GetLocalizedString(columnName, chineseCI);
//if (columnName.Equals(chineseColumnName))
// chineseColumnName = string.Empty;
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
cell.SetCellValue(columnName);
//cell = (NPOI.HSSF.UserModel.HSSFCell)chineseHeaderRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
//cell.SetCellValue(chineseColumnName);
//headerRow.SetCell(columnCount,cell);
//worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
columnCount++;
}
}
//worksheet.Cells.Rows.Add(0, headerRow);
int rowCount = 0;
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900;
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
NPOI.HSSF.UserModel.HSSFCellStyle ManualAdjustCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//ManualInputDateCellStyle.CloneStyleFrom(dateCellStyle);
ManualAdjustCellStyle.SetFont(boldFont);
//NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ;
//NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ;
foreach (DataRow row in dataTable.Rows)
{
rowCount++;
columnCount = 0;
// ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row();
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
foreach (DataColumn column in dataTable.Columns)
{
if (!column.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
//ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount);
if (column.DataType.Equals(typeof(string)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
// Override style to bold if manual adjust
if (cell.StringCellValue != string.Empty)
{
if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
}
}
else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
double value = Convert.ToDouble(row[column].ToString());
if (value.Equals(double.NaN))
{
cell.SetCellValue(string.Empty);
}
else
{
cell.SetCellValue(value);
}
}
//cell.CellStyle = numericCellStyle;
}
else if (column.DataType.Equals(typeof(int)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
}
//cell.CellStyle = integerCellStyle;
}
else if (column.DataType.Equals(typeof(DateTime)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd");
//if (row[column] == System.DBNull.Value)
// cell.Value = string.Empty;
//else
// cell.Value = (DateTime)row[column];
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue((DateTime)row[column]);
}
cell.CellStyle = dateCellStyle;
}
else
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(row[column].ToString());
}
}
//worksheet.Cells[rowCount, columnCount] = cell;
columnCount++;
}
}
// worksheet.Cells.Rows.Add(rowCount, detailRow);
}
//workbook.Worksheets.Add(worksheet);
return(worksheet);
}
else
{
return(null);
}
}