C# NPOI使用
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;
}
}
- 上一篇 »C# split 使用方法
- 下一篇 »C#操作NPOI实现Excel数据导入导出
