62,243
社区成员
发帖
与我相关
我的任务
分享c# asp.net 用poi写excel，就设置一下宽度，为啥直接报错了
难道有合并单元格的不能设置吗？
sheet.SetColumnWidth(0, 10); 的10我随便写的，因为只要写这句话就报错
...全文
请发表友善的回复…
发表回复
一品梅 2021-03-25
- 打赏
- 举报
我用的是2.0.6.0的，好像调整不大。
心在飞扬 2021-03-25
- 打赏
- 举报
当然我在网上下载了一个新的dll，可是那个新版本的dll，可能对一些命名空间的类有调整了，我换了之后，我其他结构报错了
所以不敢换
心在飞扬 2021-03-25
- 打赏
- 举报
从代码来说，只要在for里面，任何位置我都试过了，只要设置了宽度里面报错。
我一开始我怀疑是合并单元格的为题。我把合并单元格去了，也没用。
然后我的dll是1.2.1的。我也不知道是这个版本里面这个方法直接就不行还是怎么样
心在飞扬 2021-03-25
- 打赏
- 举报
/// <summary>
/// 生成excel
/// </summary>
/// <param name="dt"></param>
/// <param name="title"></param>
/// <param name="Sum"></param>
/// <returns></returns>
private HSSFWorkbook DealDataTable(List<DataTable> dt, List<string> title, List<string> Sum)
{
HSSFWorkbook saraly = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "广州xxxx公司";
saraly.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "";
saraly.SummaryInformation = si;
/*首行的标题*/
HSSFCellStyle fist1 = saraly.CreateCellStyle();
fist1.WrapText = false;
HSSFFont fist1Font = saraly.CreateFont();
fist1Font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
fist1Font.FontHeight = 480;
fist1.SetFont(fist1Font);
fist1.Alignment = HSSFCellStyle.ALIGN_CENTER;
fist1.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*表头*/
HSSFCellStyle fist2 = saraly.CreateCellStyle();
fist2.WrapText = true;
HSSFFont fist2Font = saraly.CreateFont();
fist2Font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
fist2.SetFont(fist2Font);
fist2.Alignment = HSSFCellStyle.ALIGN_CENTER;
fist2.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*内容*/
HSSFCellStyle context = saraly.CreateCellStyle();
context.WrapText = true;
HSSFFont font = saraly.CreateFont();
context.SetFont(font);
context.Alignment = HSSFCellStyle.ALIGN_CENTER;
context.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*红色文字的内容*/
HSSFCellStyle contextRed = saraly.CreateCellStyle();
contextRed.WrapText = true;
HSSFFont fontRed = saraly.CreateFont();
fontRed.Color = HSSFFont.COLOR_RED;
contextRed.SetFont(fontRed);
contextRed.Alignment = HSSFCellStyle.ALIGN_CENTER;
contextRed.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
/*红色背景的文字内容*/
HSSFCellStyle contextGood = saraly.CreateCellStyle();
contextGood.WrapText = true;
HSSFFont fontGood = saraly.CreateFont();
contextGood.SetFont(fontGood);
contextGood.Alignment = HSSFCellStyle.ALIGN_CENTER;
contextGood.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
HSSFPalette customPalette = saraly.GetCustomPalette();
customPalette.SetColorAtIndex(HSSFColor.LIGHT_BLUE.index, (byte) 228, (byte) 223, (byte)236);
contextGood.FillPattern=HSSFCellStyle.SOLID_FOREGROUND;
contextGood.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
/*循环表*/
for (int i = 0; i < dt.Count; i++)
{
HSSFSheet sheet = saraly.CreateSheet(title[i]);
int ColumnsCount = dt[i].Columns.Count;
if (dt[i].Columns.Contains("sysOutFlag"))
{
ColumnsCount = ColumnsCount - 1;
}
if (title[i] != "sheet1")
{
/*首行*/
HSSFRow rowTitle = sheet.CreateRow(0);
rowTitle.Height = 800;
HSSFCell cell = rowTitle.CreateCell(0);
cell.CellStyle = fist1;
cell.SetCellValue(title[i]);
sheet.AddMergedRegion(new Region(0, 0, 0, ColumnsCount - 1));
/*标题*/
HSSFRow rowTitle1 = sheet.CreateRow(1);
rowTitle1.Height = 730;
for (int ii = 0; ii < ColumnsCount; ii++)
{
HSSFCell cellT = rowTitle1.CreateCell(ii);
cellT.CellStyle = fist2;
cellT.SetCellValue(dt[i].Columns[ii].ToString());
cellT.SetCellType(HSSFCell.CELL_TYPE_STRING);
}
}
Dictionary<string, itemSum> a = new Dictionary<string, itemSum>();
/*内容*/
for (int ii = 0; ii < dt[i].Rows.Count; ii++)
{
HSSFRow rowContent = sheet.CreateRow(ii + (title[i] == "sheet1" ? 0 : 2));
rowContent.Height = 620;
for (int jj = 0; jj < ColumnsCount; jj++)
{
HSSFCell cellT = rowContent.CreateCell(jj);
cellT.CellStyle = context;
if (dt[i].Columns.Contains("sysOutFlag"))
{
string sysOutFlag = dt[i].Rows[ii]["sysOutFlag"].ToString();
if (sysOutFlag == "1")
{
cellT.CellStyle = contextRed;
}
else if (sysOutFlag == "2")
{
cellT.CellStyle = contextGood;
}
}
setDataColumnValue(cellT, dt[i].Columns[jj].DataType.ToString(), "", dt[i].Rows[ii][jj].ToString());
if (Sum.Contains(dt[i].Columns[jj].ColumnName))
{
if (a.ContainsKey(dt[i].Columns[jj].ColumnName))
{
a[dt[i].Columns[jj].ColumnName].toalCnt = a[dt[i].Columns[jj].ColumnName].toalCnt + decimal.Parse(dt[i].Rows[ii][jj].ToString());
}
else
{
a.Add(dt[i].Columns[jj].ColumnName, new itemSum(jj, decimal.Parse(dt[i].Rows[ii][jj].ToString())));
}
}
}
if (title[i] == "sheet1")
{
sheet.AddMergedRegion(new Region(ii, 0, ii, 10));
}
}
/*附加列*/
if (dt[i].Rows.Count > 1)
{
HSSFRow rowSum = sheet.CreateRow(dt[i].Rows.Count + 2);
rowSum.Height = 620;
foreach (KeyValuePair<string, itemSum> keyValuePair in a)
{
HSSFCell cellSum = rowSum.CreateCell(keyValuePair.Value.index);
cellSum.CellStyle = context;
setDataColumnValue(cellSum, "System.Decimal", "", keyValuePair.Value.toalCnt.ToString());
}
}
/*兼职*/
if (dt[i].Columns.Contains("教师") && dt[i].Columns.Contains("职工代码") && dt[i].Columns.Contains("类型") && dt[i].Columns.Contains("标准"))
{
HSSFCellStyle bottom = saraly.CreateCellStyle();
bottom.WrapText = false;
bottom.Alignment = HSSFCellStyle.ALIGN_CENTER;
bottom.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
bottom.BorderTop = HSSFCellStyle.BORDER_THIN;
HSSFRow rowSum = sheet.CreateRow(dt[i].Rows.Count + 4);
sheet.AddMergedRegion(new Region(dt[i].Rows.Count + 4, 0, dt[i].Rows.Count + 4, ColumnsCount - 1));
rowSum.Height = 620;
HSSFCell buttomCell = rowSum.CreateCell(0);
buttomCell.CellStyle = bottom;
if (dt[i].Columns.Contains("课时费（元）"))
{
setDataColumnValue(buttomCell, "部门负责人: 人力资源审核: 制表人: ");
}
else {
setDataColumnValue(buttomCell, "制表人 教务负责人 ");
}
}
}
return saraly;
}一品梅 2021-03-24
- 打赏
- 举报
一品梅 2021-03-24
- 打赏
- 举报
我测试的可以啊，没问题，你把全部代码发过来，还有你EXCEL的图片
一品梅 2021-03-24
- 打赏
- 举报
合并的也没啥问题啊心在飞扬 2021-03-23
- 打赏
- 举报
去了这句话，我其他的都正常的。 我就是有几个列，标题文字少，但是内容比较多，就想调整下宽度。
就是设置不成功，而且 他的这个方法，就是声明2个int类型，
不知道是不是方法内部的问题。
心在飞扬 2021-03-23
- 打赏
- 举报
客户要求~~CSV也没那么多格式
一品梅 2021-03-23
- 打赏
- 举报
感觉你还不如导出为CSV文件方便呢
