public static void CreateCell(NPOI.SS.UserModel.IRow row, int index, object value, NPOI.SS.UserModel.ICellStyle cellStyle)
{
var cell = row.CreateCell(index);
if (value is decimal)
cell.SetCellValue((double)((decimal)value));
else
if (value is int)
cell.SetCellValue((int)value);
else
cell.SetCellValue((string)value);
cell.CellStyle = cellStyle;
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return cell;
}
/// <summary>
/// 导出数据行 用于普通导出 不指明列的
/// </summary>
/// <param name="columnsCount"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(int columnsCount, DataRow drSource, NPOI.SS.UserModel.IRow currentExcelRow, NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < columnsCount; cellIndex++)
{
//列名称
NPOI.SS.UserModel.ICell newCell = null;
System.Type rowType = drSource[cellIndex].GetType();
string drValue = drSource[cellIndex].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(DateTime.Parse(drValue).ToString("yyyy-MM-dd HH:mm:ss"));
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = currentExcelRow.CreateCell(cellIndex);
//newCell.SetCellValue(intV.ToString());
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, NPOI.SS.UserModel.IRow currentExcelRow, NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkBook, NPOI.SS.UserModel.ICellStyle cellStyle_DateTime)
{
for (int cellIndex = 0; cellIndex < _listColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = _listColumnsName.GetKey(cellIndex).ToString();
NPOI.SS.UserModel.ICell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
newCell.CellStyle = cellStyle_DateTime;
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
case "System.Guid"://空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
private NPOI.SS.UserModel.ICell CreateCell(NPOI.SS.UserModel.IRow newRow, SysConext.Rectangle rectangle)
{
NPOI.SS.UserModel.ICell newCell = newRow.GetCell(rectangle.col1);
if (newCell == null)
newCell = newRow.CreateCell(rectangle.col1);
newCell.SetCellValue(rectangle.titleName);
if (rectangle.isMegerCell)
newsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rectangle.row1, rectangle.row2, rectangle.col1, rectangle.col2));
return newCell;
}
