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; }