|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
|
//创建一个workbook
HSSFWorkbook workBook = new HSSFWorkbook();
//使用PropertySetFactory的方法创建一个DocumentSummaryInfomation和一个SummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
string[] sheetHead = { "总序号", "出水编号(单天序号)", "出水单位", "出土编号", "名称", "数量", "质地", "出土时间", "发掘人", "备注", "日期"};
int p = 0;//向表头赋值时的索引
int[] sheetWidth = { 8, 10, 10, 5, 5, 20, 10, 5, 5, 20, 15, 15, 15, 10, 30, 10}; //表中每一列的宽度
//设置dsi和si各自的描述信息
dsi.Company = "陕西十月";
si.Subject = "考古信息";
si.Author = "西安唐远";
si.CreateDateTime = DateTime.Now;
si.Title = "十月文化遗产";
//将描述信息赋值给workbook
workBook.DocumentSummaryInformation = dsi;
workBook.SummaryInformation = si;
//创建excel文件的时候默认必须有一个sheet，这里创建三个，和桌面新建excel文件一致。
HSSFSheet sheet1 = workBook.CreateSheet("sheet1");
HSSFSheet sheet2 = workBook.CreateSheet("原始坐标");
HSSFSheet sheet3 = workBook.CreateSheet("坐标解算");
//创建excel文件
FileStream fs = new FileStream(@"C:\Users\lx\Desktop\test.xls", FileMode.Create, FileAccess.Write, FileShare.None);
HSSFCellStyle style = workBook.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BorderTop = CellBorderType.THIN;
style.Alignment = CellHorizontalAlignment.CENTER;
style.VerticalAlignment = CellVerticalAlignment.CENTER;
style.WrapText = true;
//创建表格为4316行，16列
for (int i = 0; i < 4316; i++)
{
HSSFRow row = sheet1.CreateRow(i);
for (int j = 0; j < 16; j++)
{
HSSFCell cell = sheet1.GetRow(i).CreateCell(j);
cell.CellStyle = style;
}
sheet1.GetRow(i).Height = 20 * 20;
}
for (int i = 0; i
{
sheet1.SetColumnWidth(i, sheetWidth[i] * 256);
}
//处理表头
for (int i = 0; i < 16; i++)//i表示第一行的列数
{
if (i == 3)//将第一行的第3列和第4列合并
{
sheet1.AddMergedRegion(new Region(0, i, 0, i + 1));
HSSFCell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellValue("出土号");
cell.CellStyle = style;
}
else if (i == 10)//将第一行的第11列和第12列和第13列合并
{
sheet1.AddMergedRegion(new Region(0, i, 0, i + 2));
HSSFCell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellValue("坐标 m");
cell.CellStyle = style;
}
else if (i != 4 && i != 11 && i != 12) //如果只是else，不写其他的话，i=4 j=4 i=11 j=11 i=12 j=12都能进的去，但是合并单元格会出问题
{
for (int j = 0; j < 16; j++)//j表示第二行的列数
{
//其他列将第1行和第2行合并
if ((i == j))
{
//如果列数相同则合并
sheet1.AddMergedRegion(new Region(0, i, 1, i));
HSSFCell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellValue(sheetHead[p++]);//0 1 2 5 6 7 8 9 13 14 15
cell.CellStyle = style;
}
}
}
}
//2行4列、5列、11列、12列、13列循环不到，单独设置值
sheet1.GetRow(1).GetCell(3).SetCellValue("地层");
sheet1.GetRow(1).GetCell(4).SetCellValue("号数");
sheet1.GetRow(1).GetCell(10).SetCellValue("N");
sheet1.GetRow(1).GetCell(11).SetCellValue("E");
sheet1.GetRow(1).GetCell(12).SetCellValue("Z");
//写入第一列，从第三行开始
for (int i = 2; i <= sheet1.LastRowNum; i++)
{
sheet1.GetRow(i).GetCell(0).SetCellValue(i - 1);
}
//处理表中元素
for (int i = 2; i <= sheet1.LastRowNum; i++) //行
{
for (int j = 1; j < sheet1.GetRow(3).LastCellNum; j++)//列
{
HSSFCell cell = sheet1.GetRow(i).GetCell(j);
cell.SetCellValue(Convert.ToInt32((i - 1) + "" + j.ToString()));
cell.CellStyle = style;
}
}
//写入workbook信息
workBook.Write(fs);
fs.Close();
Console.WriteLine("写入成功！");
Console.ReadKey();
|
加载中…
(2017-02-24 18:20:16)