在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
1 HSSFWorkbook workbook = new HSSFWorkbook(); 2 HSSFSheet sheet = workbook.CreateSheet( "Sheet1") as HSSFSheet; 3 IRow row = sheet.CreateRow(0); 4 row.Height = 30 * 20; 5 6 ICell cellTitle = row.CreateCell(0); 7 titleHeader.Alignment = HorizontalAlignment.Center; 8 titleHeader.VerticalAlignment = VerticalAlignment.Center; 9 10 style.BorderBottom = BorderStyle.Thin; 11 style.BorderLeft = BorderStyle.Thin; 12 style.BorderRight = BorderStyle.Thin; 13 14 IFont font = workbook.CreateFont(); 15 font.FontHeightInPoints = 14; 16 font.FontName = "微软雅黑"; 17 font.IsBold = true; 18 19 cellTitle.SetFont(font); 20 cellTitle.SetCellValue(titleName) 21 22 23 Color c = Color.FromArgb(215, 228, 188); 24 HSSFPalette palette = workbook.GetCustomPalette(); 25 palette.SetColorAtIndex((short)63, c.R, c.G, c.B); 26 HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B); 27 style.FillPattern = FillPattern.SolidForeground; 28 style.FillForegroundColor = cellColor.Indexed; 29 30 region = new CellRangeAddress(3, 3, 15, columnsCount - 1); 31 sheet.AddMergedRegion(region); 32 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, HSSFColor.Black.Index); 33 34 35 36 //列宽自适应,只对英文和数字有效 37 for (int i = 0; i <= columnsCount; i++) 38 { 39 sheet.AutoSizeColumn(i); 40 } 41 42 //列宽自适应中文有效 43 for (int i = 0; i < 15; i++) 44 { 45 int columnWidth = sheet.GetColumnWidth(i) / 256; 46 for (int rowNum = 4; rowNum < 6 + rowsCount; rowNum++) 47 { 48 IRow currentRow; 49 //当前行未被使用过 50 if (sheet.GetRow(rowNum) == null) 51 { 52 currentRow = sheet.CreateRow(rowNum); 53 } 54 else 55 { 56 currentRow = sheet.GetRow(rowNum); 57 } 58 59 if (currentRow.GetCell(i) != null) 60 { 61 ICell currentCell = currentRow.GetCell(i); 62 int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; 63 if (columnWidth < length) 64 { 65 columnWidth = length; 66 } 67 } 68 } 69 sheet.SetColumnWidth(i, columnWidth * 350); 70 } 71 72 //列宽自适应中文有效 73 for (int i = 15; i < columnsCount; i++) 74 { 75 int rowNum; 76 77 if (dtSource.Columns[i].ColumnName.Contains("/")) 78 { 79 rowNum = 4; 80 } 81 else 82 { 83 rowNum = 5; 84 } 85 86 int columnWidth = sheet.GetColumnWidth(i) / 256; 87 for (; rowNum < 6 + rowsCount; rowNum++) 88 { 89 IRow currentRow; 90 //当前行未被使用过 91 if (sheet.GetRow(rowNum) == null) 92 { 93 currentRow = sheet.CreateRow(rowNum); 94 } 95 else 96 { 97 currentRow = sheet.GetRow(rowNum); 98 } 99 100 if (currentRow.GetCell(i) != null) 101 { 102 ICell currentCell = currentRow.GetCell(i); 103 int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; 104 if (columnWidth < length) 105 { 106 columnWidth = length; 107 } 108 } 109 } 110 sheet.SetColumnWidth(i, columnWidth * 350); 111 } 112 113 114 //若没有数据则建立空文档 115 if (workbook.NumberOfSheets == 0) 116 { 117 HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet; 118 } 119 120 //写文件 121 MemoryStream ms = new MemoryStream(); 122 workbook.Write(ms); 123 ms.Flush(); 124 ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0; 125 126 return ms; 127 128 if (j == 14 ) 129 { 130 double db = 0; 131 if (double.TryParse(objVal.ToString(), out db)) 132 { 133 cell.SetCellValue(db); 134 } 135 } 136 else 137 { 138 SetCellValue(cell, objVal); 139 } 140 141 public static void SetCellValue(ICell eCell, object data) 142 { 143 string typeStr = data.GetType().ToString(); 144 145 switch (typeStr) 146 { 147 case "System.String": 148 eCell.SetCellValue(data.ToString()); 149 break; 150 case "System.DateTime": 151 System.DateTime dateV; 152 System.DateTime.TryParse(data.ToString(), out dateV); 153 eCell.SetCellValue(dateV.ToString("yyyy/MM/dd")); 154 break; 155 case "System.Boolean": 156 bool boolV = false; 157 bool.TryParse(data.ToString(), out boolV); 158 eCell.SetCellValue(boolV); 159 break; 160 case "System.Int16": 161 case "System.Int32": 162 case "System.Int64": 163 case "System.Byte": 164 int intV = 0; 165 int.TryParse(data.ToString(), out intV); 166 eCell.SetCellValue(intV); 167 break; 168 case "System.Decimal": 169 case "System.Double": 170 double doubV = 0; 171 double.TryParse(data.ToString(), out doubV); 172 eCell.SetCellValue(doubV); 173 break; 174 case "System.DBNull": 175 eCell.SetCellValue(""); 176 break; 177 default: 178 eCell.SetCellValue(""); 179 break; 180 } 181 }
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet( "Sheet1") as HSSFSheet;
IRow row = sheet.CreateRow(0);
row.Height = 30 * 20;
ICell cellTitle = row.CreateCell(0);
titleHeader.Alignment = HorizontalAlignment.Center;
titleHeader.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 14;
font.FontName = "微软雅黑";
font.IsBold = true;
cellTitle.SetFont(font);
cellTitle.SetCellValue(titleName)
Color c = Color.FromArgb(215, 228, 188);
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)63, c.R, c.G, c.B);
HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B);
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = cellColor.Indexed;
region = new CellRangeAddress(3, 3, 15, columnsCount - 1);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, HSSFColor.Black.Index);
//列宽自适应,只对英文和数字有效
for (int i = 0; i <= columnsCount; i++)
{
sheet.AutoSizeColumn(i);
}
//列宽自适应中文有效
for (int i = 0; i < 15; i++)
{
int columnWidth = sheet.GetColumnWidth(i) / 256;
for (int rowNum = 4; rowNum < 6 + rowsCount; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(i) != null)
{
ICell currentCell = currentRow.GetCell(i);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(i, columnWidth * 350);
}
//列宽自适应中文有效
for (int i = 15; i < columnsCount; i++)
{
int rowNum;
if (dtSource.Columns[i].ColumnName.Contains("/"))
{
rowNum = 4;
}
else
{
rowNum = 5;
}
int columnWidth = sheet.GetColumnWidth(i) / 256;
for (; rowNum < 6 + rowsCount; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(i) != null)
{
ICell currentCell = currentRow.GetCell(i);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(i, columnWidth * 350);
}
//若没有数据则建立空文档
if (workbook.NumberOfSheets == 0)
{
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
}
//写文件
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0;
return ms;
if (j == 14 )
{
double db = 0;
if (double.TryParse(objVal.ToString(), out db))
{
cell.SetCellValue(db);
}
}
else
{
SetCellValue(cell, objVal);
}
public static void SetCellValue(ICell eCell, object data)
{
string typeStr = data.GetType().ToString();
switch (typeStr)
{
case "System.String":
eCell.SetCellValue(data.ToString());
break;
case "System.DateTime":
System.DateTime dateV;
System.DateTime.TryParse(data.ToString(), out dateV);
eCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));
break;
case "System.Boolean":
bool boolV = false;
bool.TryParse(data.ToString(), out boolV);
eCell.SetCellValue(boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(data.ToString(), out intV);
eCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = 0;
double.TryParse(data.ToString(), out doubV);
eCell.SetCellValue(doubV);
break;
case "System.DBNull":
eCell.SetCellValue("");
break;
default:
eCell.SetCellValue("");
break;
}
}
|
请发表评论