一，创建单元格格式
一，单元格格式
提前设置单元格格式；如果有表头，列名等字段需要用到不同的格式，可以添加多个style，一个IWorkbook最多可以有4000个cellStyle，所以相同格式可以设置成静态变量，避免重复创建相同格式的cellStyle。本文只介绍关于HSSFWorkbook的导出步骤，因为XSSFWorkbook好像没有SetEnclosedBorderOfRegion方法，合并单元格的时候边框设置有问题，不太好看。本人对NPOI了解太浅，有了解的伙伴儿还望告知有没有类似的实现方法。
private void SetCellSty(IWorkbook workbook)
{
ICellStyle cellstyle = workbook.CreateCellStyle();
//下边框
cellstyle.BorderBottom = BorderStyle.Thin;
//上边框
cellstyle.BorderTop = BorderStyle.Thin;
//左边框
cellstyle.BorderLeft = BorderStyle.Thin;
//右边框
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑";//字体
font.Color = 8;//颜色
//font.FontHeight = 15;//字体高度
font.FontHeightInPoints = 20;//字体高度
font.IsBold = true;//是否加粗
font.IsItalic = false;//是否斜体
font.IsStrikeout = false;//是否加删除线
cellstyle.SetFont(font);
//设置前景色
//cellstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
//cellstyle.FillPattern = FillPattern.SolidForeground;
cellStyle_Title = cellstyle;;
}
cell背景是使用前景色FillForegroundColor属性；不要使用FillBackgroundColor，背景色会把单元格内的字体遮住；
二，创建IWorlBook
private string SaveFilePath()
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Title = "请选择文件存放路径";
saveFile.Filter = "Excel文档(*.xls)|*.xls";
if (saveFile.ShowDialog() != DialogResult.OK)
{
return null;
}
return saveFile.FileName;
}
private void tButton1_Click(object sender, EventArgs e)
{
string path = SaveFilePath();
if (".XLS".Equals(Path.GetExtension(path).ToUpper()))
{
IWorkbook workbook = new HSSFWorkbook();
SetCellSty(workbook);
workbook = CreateWorkBook(workbook);
workbook = DataTableToWorkBook(workbook, true);
ExportToExcel(workbook, path);
}
//else if (".XLSX".Equals(Path.GetExtension(path).ToUpper()))
//{
// IWorkbook workbook = new XSSFWorkbook();
// InitializesCellStyle(workbook);
// workbook = CreateWorkBook(workbook);
// workbook = DataTableToWorkBook(workbook, false);
// ExportToExcel(workbook, path);
//}
}
private IWorkbook CreateWorkBook(IWorkbook workbook)
{
ISheet sheet = workbook.CreateSheet("设备保养表");
sheet.DefaultColumnWidth = 3;
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 30));//设备保养表
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 31, 32));//设备
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 33, 35));//设备编号
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 33, 35));//设备类型
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 36, 47));//设备编号text
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 36, 47));//设备类型text
sheet.AddMergedRegion(new CellRangeAddress(3, 4, 42, 43));//记录方法
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 44, 46));//Spec in
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 44, 46));//Spec out
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("设备保养表 PM Sheet");
cell.CellStyle = cellStyle_Title;
ICell cell_eqType = sheet.CreateRow(1).CreateCell(33);
cell_eqType.SetCellValue("设备类型");
cell_eqType.CellStyle = cellStyle_Body;
ICell cell_Record = sheet.CreateRow(3).CreateCell(42);
cell_Record.SetCellValue("记录方法");
cell_Record.CellStyle = cellStyle_Body;
ICell cell_out = sheet.CreateRow(4).CreateCell(44);
cell_out.SetCellValue("Spec Out");
cell_out.CellStyle = cellStyle_Body;
ICell cell_EQ = sheet.GetRow(0).CreateCell(31);
cell_EQ.SetCellValue("设备");
cell_EQ.CellStyle = cellStyle_Body;
ICell cell_EQName = sheet.GetRow(0).CreateCell(36);
cell_EQName.SetCellValue(eqName);
cell_EQName.CellStyle = cellStyle_Body;
ICell cell_EQType = sheet.GetRow(1).CreateCell(36);
cell_EQType.SetCellValue(eqType);
cell_EQType.CellStyle = cellStyle_Body;
ICell cell_eqName = sheet.GetRow(0).CreateCell(33);
cell_eqName.SetCellValue("设备编号");
cell_eqName.CellStyle = cellStyle_Body;
ICell cell_in = sheet.GetRow(3).CreateCell(44);
cell_in.SetCellValue("Spec In");
cell_in.CellStyle = cellStyle_Body;
ICell cell_O = sheet.GetRow(3).CreateCell(47);
cell_O.SetCellValue("O");
cell_O.CellStyle = cellStyle_Body;
ICell cell_X = sheet.GetRow(4).CreateCell(47);
cell_X.SetCellValue("X");
cell_X.CellStyle = cellStyle_Body;
return workbook;
}
titile大概就是这个样子,合并的单元格现在没有边框的原因是少了一些设置，代码在下面。
三，添加行数据
/// <param name="dataSource">数据源表</param>
/// <param name="workbook">工作簿</param>
/// <param name="rowId">开始行号 </param>
private int SetCell(System.Data.DataTable dataSource, IWorkbook workbook, int rowId)
{
IRow row = sheet.CreateRow(rowId);
{
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 1, 6));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 7, 10));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 11, 13));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 14, 16));
row.CreateCell(0).SetCellValue(tableDaily.Columns[0].ColumnName.ToString());
row.CreateCell(1).SetCellValue(tableDaily.Columns[1].ColumnName.ToString());
row.CreateCell(7).SetCellValue(tableDaily.Columns[2].ColumnName.ToString());
row.CreateCell(11).SetCellValue(tableDaily.Columns[3].ColumnName.ToString());
row.CreateCell(14).SetCellValue(tableDaily.Columns[4].ColumnName.ToString());
}
for (int j = 5; j < columnsCount; j++)
{
row.CreateCell(16 + j - 4).SetCellValue(tableDaily.Columns[j].ColumnName.ToString());
}
foreach (ICell cell in row.Cells)
{
cell.CellStyle = cellStyle_Header;
}
//row.RowStyle = cellStyle_Header;
rowId ++ ;//向下一行
for (int i = 0; i < rowCount; i++)
{
DataRow dataRow = tableDaily.Rows[i];
IRow irow = sheet.CreateRow(rowId);
//irow.RowStyle = cellStyle_Body;
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 1, 6));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 7, 10));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 11, 13));
sheet.AddMergedRegion(new CellRangeAddress(rowId, rowId, 14, 16));
//CellRangeAddress region1 = new CellRangeAddress(rowId, rowId, 1, 6);
//CellRangeAddress region2 = new CellRangeAddress(rowId, rowId, 7, 10);
//CellRangeAddress region3 = new CellRangeAddress(rowId, rowId, 11, 13);
//CellRangeAddress region4 = new CellRangeAddress(rowId, rowId, 14, 16);
//sheet.AddMergedRegion(region1);
//sheet.AddMergedRegion(region2);
//sheet.AddMergedRegion(region3);
//sheet.AddMergedRegion(region4);
irow.CreateCell(0).SetCellValue(dataRow[0].ToString());
irow.CreateCell(1).SetCellValue(dataRow[1].ToString());
irow.CreateCell(7).SetCellValue(dataRow[2].ToString());
irow.CreateCell(11).SetCellValue(dataRow[3].ToString());
irow.CreateCell(14).SetCellValue(dataRow[4].ToString());
//下面这一坨就是设置合并单元格的边框，因为每个都要设置一遍太麻烦，所以循环设置的代码在最后面
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region3, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region4, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
for (int j = 5; j < columnsCount; j++)
{
irow.CreateCell(16 + j - 4).SetCellValue(dataRow[j].ToString());
}
foreach (ICell cell in irow.Cells)
{
if ("X".Equals(cell.StringCellValue))
{
cell.CellStyle = cellStyle_XColor;
}
else
{
cell.CellStyle = cellStyle_Body;
}
}
//irow.RowStyle = cellStyle_Body;
rowId ++;
}
return rowId ++;
}
}
生成的样子应该是这样的。红色背景是设置的如果为X就把背景变为红色
四，导出
private void ExportToExcel(IWorkbook workbook, string filePath)
{
try
{
//为每个合并单元格设置边框
List<CellRangeAddress> cellRanges = workbook.GetSheetAt(0).MergedRegions;
foreach (CellRangeAddress region in cellRanges)
{
((HSSFSheet)workbook.GetSheetAt(0)).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
}
FileStream fs = null;
try
{
using (fs = new FileStream(filePath, FileMode.Append, FileAccess.Write))
{
workbook.Write(fs);
}
MessageBox.Show("Export successfully.");
}
finally
{
fs.Close();
workbook.Close();
}
}
catch
{
MessageBox.Show("Execl is using.");
}
}
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
