前言
没有进行格式化导出的excel文件内容不是很看,对观看的人还需要进行修改,加大了工作量,因此使用导出时对excel格式就进行优化
优化代码
1 数据准备
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
public dynamic ExportLogisticsOrderExcel()
{
var ds = SqlDapperHelper.ExecuteDataSet("Sql语句", new {参数名=参数}, HttpHelper.OpenID());
DataTable dt = ds.Tables["1"];
// 创建一个新的 DataTable 以保存选定列的数据
DataTable selectedColumnsData = new DataTable();
// 添加需要的列到新 DataTable
string[] fixedColumns = new string[] { "名称", "规格", "详情" };
foreach (string col in fixedColumns)
{
selectedColumnsData.Columns.Add(col, typeof(string));
}
// 添加每天的列到新 DataTable
for (int i = 1; i <= 31; i++)
{
selectedColumnsData.Columns.Add(i + "日", typeof(string));
}
// 遍历原始 DataTable,选择指定列的数据
foreach (DataRow row in dt.Rows)
{
// 从原始 DataTable 中获取指定列的数据
string Name = row["Name"].ToString();
string Novmg = row["Novmg"].ToString();
string[] SalesInfo = row["SalesInfo"].ToString().Split(new string[] { "," }, StringSplitOptions.None);
selectedColumnsData.Rows.Add(Name, Novmg, "生成数据", SalesInfo[0], SalesInfo[1], SalesInfo[2],
SalesInfo[3], SalesInfo[4], SalesInfo[5], SalesInfo[6], SalesInfo[7], SalesInfo[8], SalesInfo[9], SalesInfo[10],
SalesInfo[11], SalesInfo[12], SalesInfo[13], SalesInfo[14], SalesInfo[15], SalesInfo[16], SalesInfo[17], SalesInfo[18],
SalesInfo[19], SalesInfo[20], SalesInfo[21], SalesInfo[22], SalesInfo[23], SalesInfo[24], SalesInfo[25], SalesInfo[26],
SalesInfo[27], SalesInfo[28], SalesInfo[29], SalesInfo[30]);
string[] OrderQuantityInfo = row["OrderQuantityInfo"].ToString().Split(new string[] { "," }, StringSplitOptions.None);
selectedColumnsData.Rows.Add(Name, Novmg, "使用数据", OrderQuantityInfo[0], OrderQuantityInfo[1], OrderQuantityInfo[2],
OrderQuantityInfo[3], OrderQuantityInfo[4], OrderQuantityInfo[5], OrderQuantityInfo[6], OrderQuantityInfo[7], OrderQuantityInfo[8],
OrderQuantityInfo[9], OrderQuantityInfo[10], OrderQuantityInfo[11], OrderQuantityInfo[12], OrderQuantityInfo[13], OrderQuantityInfo[14],
OrderQuantityInfo[15], OrderQuantityInfo[16], OrderQuantityInfo[17], OrderQuantityInfo[18], OrderQuantityInfo[19], OrderQuantityInfo[20],
OrderQuantityInfo[21], OrderQuantityInfo[22], OrderQuantityInfo[23], OrderQuantityInfo[24], OrderQuantityInfo[25], OrderQuantityInfo[26],
OrderQuantityInfo[27], OrderQuantityInfo[28], OrderQuantityInfo[29], OrderQuantityInfo[30]);
}
var res = NPOIExtension.ExportLogisticsOrderExcel(selectedColumnsData);
return res;
}
2 excel格式美化
/// <summary>
/// 合并单元格 美化格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="c"></param>
/// <returns></returns>
public static dynamic SheetAddMergedRegion(ISheet sheet, int c)
{
c = c - 1;
for (int i = 1; i <= c; i += 2)
{
// 合并相同数据的行为一个单元格
for (int j = 0; j < 4; j++)
{
CellRangeAddress cellRange = new CellRangeAddress(i, i + 1, j, j);
sheet.AddMergedRegion(cellRange);
}
}
// 获取工作表的行数和列数
int rowCount = sheet.LastRowNum + 1;
int colCount = 0;
for (int i = 0; i < rowCount; i++)
{
colCount = Math.Max(colCount, sheet.GetRow(i)?.LastCellNum ?? 0);
}
// 设置单元格样式为居中
ICellStyle centerStyle = sheet.Workbook.CreateCellStyle();
centerStyle.Alignment = HorizontalAlignment.Center;
centerStyle.VerticalAlignment = VerticalAlignment.Center;
// 遍历每个单元格并应用居中样式
for (int i = 0; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
for (int j = 0; j < colCount; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
cell.CellStyle = centerStyle;
// 设置单元格宽度(列宽)
int columnWidth = 30; // 以字符数为单位
if (j > 1) { columnWidth = 12; }
sheet.SetColumnWidth(j, columnWidth * 256); // 设置第一列的宽度
}
row.Height = 400;
}
}
return sheet;
}
3 数据保存
public static dynamic ExportLogisticsOrderExcel(DataTable dataTable)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet表名称");
DataRow headerRow = dataTable.Rows[0];
IRow row = sheet.CreateRow(0);
for (int i = 0; i < headerRow.ItemArray.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(headerRow.Table.Columns[i].ColumnName);
}
// 写入数据
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dataRow = dataTable.Rows[i];
IRow rowData = sheet.CreateRow(i + 1);
for (int j = 0; j < dataRow.ItemArray.Length; j++)
{
ICell cell = rowData.CreateCell(j);
cell.SetCellValue(dataRow[j].ToString());
}
}
SheetAddMergedRegion(sheet, dataTable.Rows.Count);
string outputPath = "输出目录地址";
// 创建文件夹
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
// 保存文件
string filePath = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
using (FileStream fileStream = new FileStream(outputPath + filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
return filePath;
}
总结
c#代码实现excel表数据格式化后输出的文件美观使用者也方便。