using (NPOI.SS.UserModel.Workbook wkbook = new HSSFWorkbook())
{
using (Sheet sheet = wkbook.CreateSheet("本部计划"))
{
HSSFCellStyle dateStyle = (HSSFCellStyle)wkbook.CreateCellStyle();
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
//Row heardrow = sheet.CreateRow(0);
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// Cell heardCell = heardrow.CreateCell(j);
// heardCell.SetCellValue(dt.Columns[j].ColumnName);
//}
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// heardrow = sheet.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// Cell heardCell = heardrow.CreateCell(j);
// heardCell.SetCellValue(dt.Rows[i][j].ToString());
// }
//}
int rowIndex = 0;
foreach (DataRow row in dt.Rows)
{
#region 新建表,填充表头,填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
wkbook.CreateSheet("本部计划");
}
#region 列头及样式
{
CellStyle style = wkbook.CreateCellStyle();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);//第一行
HSSFCellStyle headStyle = (HSSFCellStyle)wkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;//左右居中
//headStyle.VerticalAlignment = VerticalAlignment.CENTER;//上下居中
//headStyle.WrapText = true;//自动换行
HSSFFont font = (HSSFFont)wkbook.CreateFont();//设置字体
//设置单元格边框
style.BorderBottom = CellBorderType.THIN;//下边框为细线边框
style.BorderLeft = CellBorderType.THIN;//左边框
style.BorderRight = CellBorderType.THIN;//上边框
style.BorderTop = CellBorderType.THIN;//右边框
style.FillForegroundColor = HSSFColor.YELLOW.index;//背景色为黄色
style.FillPattern = FillPatternType.SOLID_FOREGROUND;//填充图案为全色
// heardrow.HeightInPoints = 50f; //- 设置行高
//font.FontName = "新細明體";//字体类型
//font.FontHeightInPoints = 10;//设置字体大小
font.Boldweight = short.MaxValue;//粗体显示
style.SetFont(font);//选择需要用到的字体格式
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = style;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 :"2012-08-10"的宽度为2500
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dt.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
CellStyle style = wkbook.CreateCellStyle();//样式
style.BorderBottom = CellBorderType.THIN;//下边框为细线边框
style.BorderLeft = CellBorderType.THIN;//左边框
style.BorderRight = CellBorderType.THIN;//上边框
style.BorderTop = CellBorderType.THIN;//右边框
newCell.CellStyle = style;
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
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 = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (FileStream fs = File.OpenWrite(templatePath))
{
wkbook.Write(fs);
}
//sheet.AutoSizeColumn(1);
}
}
怎样才能全部显示边框,求指教,谢谢!
------解决思路----------------------
调试下看看呗,是不是每一行
------解决思路----------------------
dateStyle是干什么的
------解决思路----------------------
sorry,没注意看,仔细看了一下你的代码,发现代码部分貌似都没有问题。
你加个Try Catch吧,要不然调试中断什么的。。。