C#导出Excel单元格合并问题？？求大神
我想把相同部分的数据的单元格合并，请问应该怎么写？？string[]BiaoTou={"填写人","填写时间","标段名称","作业时间","作业人数","天气","作业...
我想把相同部分的数据的单元格合并，请问应该怎么写？？ string[] BiaoTou = { "填写人", "填写时间", "标段名称", "作业时间", "作业人数", "天气", "作业位置", "作业类型", "数量", "人数", "内容" }; cells[2, 0].PutValue(BiaoTou[0]); cells[2, 1].PutValue(BiaoTou[1]); cells[2, 2].PutValue(BiaoTou[2]); cells[2, 3].PutValue(BiaoTou[3]); cells[2, 4].PutValue(BiaoTou[4]); cells[2, 5].PutValue(BiaoTou[5]); cells[2, 6].PutValue(BiaoTou[6]); cells[2, 7].PutValue(BiaoTou[7]); cells[2, 8].PutValue(BiaoTou[8]); cells[2, 9].PutValue(BiaoTou[9]); cells[2, 10].PutValue(BiaoTou[10]); for (int i = 0; i < dt.Rows.Count; i++) { string Creater = dt.Rows[i]["Creater"].ToString(); string CreateTime = dt.Rows[i]["CreateTime"].ToString(); string BiaoDuanName = dt.Rows[i]["BiaoDuanName"].ToString(); string ZuoYeTime = dt.Rows[i]["ZuoYeTime"].ToString(); string ZuoYeRenShu = dt.Rows[i]["ZuoYeRenShu"].ToString(); string TianQiName = dt.Rows[i]["TianQiName"].ToString(); cells[i + 3, 0].PutValue(Creater); cells[i + 3, 1].PutValue(CreateTime); cells[i + 3, 2].PutValue(BiaoDuanName); cells[i + 3, 3].PutValue(ZuoYeTime); cells[i + 3, 4].PutValue(ZuoYeRenShu); cells[i + 3, 5].PutValue(TianQiName); cells[i + 3, 6].PutValue(dt.Rows[i]["ZuoYeLeiXingName"]); cells[i + 3, 7].PutValue(dt.Rows[i]["ZuoYeWeiZhiName"]); cells[i + 3, 8].PutValue(dt.Rows[i]["ZuoYeCount"]); cells[i + 3, 9].PutValue(dt.Rows[i]["ZuoYeRenCount"]); cells[i + 3, 10].PutValue(dt.Rows[i]["ZuoYeContent"]); }
展开
1个回答
展开全部
参考一下，使用NPOI类库，
创建Excel ，组织好数据格式，行合并等等，导出到Excel文件即可。
private static void createExcel()
{
//建立空白工作簿
IWorkbook workbook = new HSSFWorkbook();
//在工作簿中：建立空白工作表
ISheet sheet = workbook.CreateSheet("表1矿山基本情况表（1）");
//sheet.SetColumnWidth(0, 10 * 256);
//在工作表中：建立行，参数为行号，从0计
IRow row = sheet.CreateRow(0);
//在行中：建立单元格，参数为列号，从0计
ICell cell = row.CreateCell(0);
//设置单元格内容
cell.SetCellValue("表1 矿山基本情况表（1）");
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 14;
//设置字体加粗样式
//font.Boldweight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style;
//设置单元格的高度
row.Height = 30 * 20;
//设置单元格的宽度
sheet.SetColumnWidth(0, 30 * 256);
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
//创建header
ICellStyle headerStyle = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont heanderfont = workbook.CreateFont();
heanderfont.FontName = "宋体";
heanderfont.FontHeightInPoints = 11;
//使用SetFont方法将字体样式添加到单元格样式中
headerStyle.SetFont(heanderfont);
IRow header = sheet.CreateRow(1);
ICell cellHeader = CreateHeaderRowCell(header, 0, "序号", headerStyle);
cellHeader = CreateHeaderRowCell(header, 1, "矿山名称", headerStyle);
cellHeader = CreateHeaderRowCell(header, 2, "采矿许可证号", headerStyle);
cellHeader = CreateHeaderRowCell(header, 3, "矿山地址", headerStyle);
cellHeader = CreateHeaderRowCell(header, 4, "矿山中心点坐标", headerStyle);
//cellHeader = CreateHeaderRowCell(header, 5, "");
cellHeader = CreateHeaderRowCell(header, 6, "矿区面积", headerStyle);
cellHeader = CreateHeaderRowCell(header, 7, "拐点坐标", headerStyle);
cellHeader = CreateHeaderRowCell(header, 8, "矿山规模", headerStyle);
cellHeader = CreateHeaderRowCell(header, 9, "开采矿种", headerStyle);
cellHeader = CreateHeaderRowCell(header, 10, "开采矿类", headerStyle);
cellHeader = CreateHeaderRowCell(header, 11, "经济类型", headerStyle);
cellHeader = CreateHeaderRowCell(header, 12, "开采方式", headerStyle);
cellHeader = CreateHeaderRowCell(header, 13, "有效期起", headerStyle);
cellHeader = CreateHeaderRowCell(header, 14, "有效期止", headerStyle);
IRow header2 = sheet.CreateRow(2);
cellHeader = CreateHeaderRowCell(header2, 4, "东经", headerStyle);
cellHeader = CreateHeaderRowCell(header2, 5, "北纬", headerStyle);
cellHeader = CreateHeaderRowCell(header2, 6, "（km²）", headerStyle);
//合并
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));//前3列合并
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3));
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 5));
//5，6，7列不合并
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 7, 7));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 8, 8));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 9, 9));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 10, 10));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 11, 11));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 12, 12));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 13, 13));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 14, 14));
int icolIndex = 15;
//Datas
int length = 10;
for (int i = 1; i <= length; i++)
{
IRow dRow = sheet.CreateRow(2 + i);
ICell cellRow = CreateHeaderRowCell(dRow, 0, i.ToString(), headerStyle);
}
int bottomIndex = 2 + length + 1;
StringBuilder sb = new StringBuilder();
sb.Append("填表单位（盖章）： 填表人： 审核人： 填表日期： 年 月 日");
sheet.AddMergedRegion(new CellRangeAddress(bottomIndex, bottomIndex, 0, 15));
IRow bottomRow = sheet.CreateRow(bottomIndex);
ICell cellBottom = CreateHeaderRowCell(bottomRow, 0, sb.ToString(), style);
for (int columnNum = 0; columnNum <= 14; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int len = 0;
if (currentCell != null)
{
len = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
}
if (columnWidth < len)
{
columnWidth = len;
}
//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
//int width = (int)((columnWidth+0.8) * 256);
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
if (i == 4 || i == 5)
continue;
sheet.AutoSizeColumn(i, false);
}
//sheet.AutoSizeColumn(0, true);
//将工作簿写入文件
using (FileStream fs = new FileStream("生成效果1.xls", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}如果对于格式有严格要求，可以先加载Excel模板，向模板中填充数据，导出到excel文件即可。
String fileName = @"表1.xlsx";
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = new XSSFWorkbook(file);
ISheet ws = hssfworkbook.GetSheet("表1矿山基本情况表（1）");
int totalIndex = 20; // 每个类别的总行数
int rowIndex = 3; // 起始行
int colmunNum = 15;//列数
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 14;
//设置字体加粗样式
//font.Boldweight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//datas
ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont heanderfont = hssfworkbook.CreateFont();
heanderfont.FontName = "宋体";
heanderfont.FontHeightInPoints = 11;
//使用SetFont方法将字体样式添加到单元格样式中
headerStyle.SetFont(heanderfont);
//Datas
int datas_len = 10;
for (int i = 1; i <= datas_len; i++)
{
IRow dRow = ws.CreateRow(2 + i);
dRow.Height = 25 * 20;
ICell cellRow = CreateHeaderRowCell(dRow, 0, i.ToString(), headerStyle);
}
//追加最后一行 合并
int bottomIndex = 2 + datas_len + 1;
StringBuilder sb = new StringBuilder();
sb.Append("填表单位（盖章）： 填表人： 审核人： 填表日期： 年 月 日");
ws.AddMergedRegion(new CellRangeAddress(bottomIndex, bottomIndex, 0, colmunNum-1));
IRow bottomRow = ws.CreateRow(bottomIndex);
ICell cellBottom = CreateHeaderRowCell(bottomRow, 0, sb.ToString(), style);
//转存
using (FileStream fs = new FileStream("生成效果2.xlsx", FileMode.Create, FileAccess.Write))
{
hssfworkbook.Write(fs);
}
Console.WriteLine("Finish");
已赞过
已踩过<
评论
收起
你对这个回答的评价是？
推荐律师服务：
若未解决您的问题，请您详细描述您的问题，通过百度律临进行免费专业咨询
|
广告 您可能关注的内容 |
