/// <summary> /// 设置单元格内容 /// </summary> public static void SetCellTitleStyle(string sheetName, string colName, int row, int col, int rowHeight, string value,bool isCreate) { ISheet sheet = workbook.GetSheet(sheetName); IRow dataRow; if (isCreate) { dataRow = sheet.CreateRow(row); } else { dataRow = sheet.GetRow(row); } ICell colCell = dataRow.CreateCell(col); //应用单元格样式 ICellStyle titleStyle = NPOIHelper.SetCellStyle(workbook, BorderStyle.Thin, HSSFColor.Black.Index, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.Orange.Index); IFont titleFont = NPOIHelper.SetFont(workbook, "宋体", 0, 700, 10); titleStyle.SetFont(titleFont); colCell.CellStyle = titleStyle; //设置单元格标题 colCell.SetCellValue(colName); if (!ValidateHelper.IsNullOrEmpty(value)) { ICell valCell = dataRow.CreateCell(col + 1); //应用单元格样式 valCell.CellStyle = GetCellStyle();; //设置单元格标题 valCell.SetCellValue(value); } dataRow.HeightInPoints = rowHeight; }
/// <summary> /// 写入表数据 /// </summary> public static int SetCellListValue(DataTable dt, string sheetName, int row) { int rowIndex = row; int i = 1; ISheet sheet = workbook.GetSheet(sheetName); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow r in dt.Rows) { var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); var drValue = r[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (IsNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } if(dt.Rows.Count - i == 0) { ICellStyle sStyle = NPOIHelper.SetCellStyle(workbook, BorderStyle.Thin, HSSFColor.Black.Index, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.SkyBlue.Index); newCell.CellStyle = GetModificationCellStyle(sStyle); } else { newCell.CellStyle = GetCellStyle(); } //单元格宽度自适应属性 int colWidth = sheet.GetColumnWidth(column.Ordinal) / 256; //获取当前列宽度 int length = Encoding.UTF8.GetBytes(r[column].ToString()).Length; //获取当前单元格的内容宽度 //若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符 if (colWidth < length + 1) { colWidth = length + 1; } sheet.SetColumnWidth(column.Ordinal, colWidth * 256); } //行高自适应 ICell currCell = dataRow.GetCell(1); int len = Encoding.UTF8.GetBytes(currCell.ToString()).Length; dataRow.HeightInPoints = 20 * (len / 60 + 1); rowIndex++; i++; } return rowIndex; }