NPOI源代码，适合项目开发

将Sheet转化为IEnumerable

01	/// <summary>

02	/// 将Sheet表转化为IEnumerable

03	/// </summary>

04	/// <param name="sheet"></param>

05	/// <returns></returns>

06	public static IEnumerable<Row> AsIEnumerable(this Sheet sheet)

07

{

08	var itor = sheet.GetRowEnumerator();

09	while (itor.MoveNext())

10

{

11	yield return (Row)itor.Current;

12

}

13

}

[代码] 将Sheet的Row转化为IEnumerable

01	/// <summary>

02	/// 将Excel的行转化为IEnumerable

03	/// </summary>

04	/// <param name="row"></param>

05	/// <returns></returns>

06	public static IEnumerable<Cell> AsIEnumerable(this Row row)

07

{

08	var itor = row.GetCellEnumerator();

09	while (itor.MoveNext())

10

{

11	yield return (Cell)itor.Current;

12

}

13

}

[代码] 应用举例

01	// 将Excel的第一个Sheet输出为csv文件

02	using (var fs = new FileStream(@"D:\Source.xls", FileMode.Open, FileAccess.Read))

03	using (var workbook = new HSSFWorkbook(fs))

04

{

05	var sheet = workbook.GetSheetAt(0);

06

07

//扩展方法风格

08	var content = sheet.AsIEnumerable()

09	.Select(row => string.Join(",", row.AsIEnumerable().Select(cell => cell.ToString())));

10

11

//Linq风格

12	var content1 = from row in sheet.AsIEnumerable()

13	select string.Join(",", from cell in row.AsIEnumerable() select cell.ToString());

14

15	File.WriteAllLines(@"D:\Result.csv", content);

16	File.WriteAllLines(@"D:\Result1.csv", content1);

17

}

[代码] 将数组导入Excel

001	using NPOI.Util;

002	using NPOI.HSSF.Model ;

003	using NPOI.HSSF.UserModel ;

004	using NPOI.HSSF ;

005

006

......

007

008	Code// 将数据集转换到Excel： ConvertDataTableToExcel   ConvertDataGridViewToExcel

009	// 目前支持的数据类型有:DataTable,二维数组,二维交错数组,DataGridView,ArrayList

010	// 2010.01.03 采用NPOI类库，改善操作速度,便于扩展

011

012

///

013	/// 将数据集导出到Excel文件

014

///

015

/// 一维数组

016	/// Excel文件名称

017

/// 工作簿名称

018	/// 是否转换成功

019	public static bool ConvertToExcel(T[] data,string xlsSaveFileName,string sheetName)

020

{

021	FileStream fs = new FileStream (xlsSaveFileName, FileMode.Create ) ;

022

try

023

{

024	HSSFWorkbook newBook = new HSSFWorkbook () ;

025	HSSFSheet    newSheet =(HSSFSheet ) newBook.CreateSheet (sheetName ) ;//新建工作簿

026	HSSFRow      newRow = (HSSFRow )newSheet.CreateRow(0) ;//创建行

027	for (int i = 0 ; i //写入数据

028

}

029	newBook .Write (fs ) ;

030	return true ;

031

}

032	catch (Exception err)

033

{

034	throw new Exception ("转换数据到Excel失败："+err.Message ) ;

035

}

036

finally

037

{

038	fs.Close () ;

039

}

040

}

041

///

042	/// 将数据集导出到Excel文件

043

///

044

/// 二维数组

045	/// Excel文件名称

046

/// 工作簿名称

047	/// 是否转换成功

048	public static bool ConvertToExcel(T[,]  data,string xlsSaveFileName,string sheetName)

049

{

050	FileStream fs = new FileStream (xlsSaveFileName, FileMode.Create ) ;

051

try

052

{

053	HSSFWorkbook newBook = new HSSFWorkbook () ;

054	HSSFSheet    newSheet =(HSSFSheet ) newBook.CreateSheet (sheetName ) ;//新建工作簿

055	for (int i = 0 ; i //创建行

056	for (int j = 0 ; j //写入数据

057

}

058

}

059	newBook .Write (fs ) ;

060	return true ;

061

}

062	catch (Exception err)

063

{

064	throw new Exception ("转换数据到Excel失败："+err.Message ) ;

065

}

066

finally

067

{

068	fs.Close () ;

069

}

070

}

071

///

072	///         ///

073	/// 将数据集导出到Excel文件

074

///

075

/// 交错数组

076	/// Excel文件名称

077

/// 工作簿名称

078	/// 是否转换成功

079

///

080	public static bool ConvertToExcel(T[][] data,string xlsSaveFileName,string sheetName)

081

{

082	FileStream fs = new FileStream (xlsSaveFileName, FileMode.Create ) ;

083

try

084

{

085	HSSFWorkbook newBook = new HSSFWorkbook () ;

086	HSSFSheet    newSheet =(HSSFSheet ) newBook.CreateSheet (sheetName ) ;//新建工作簿

087	for (int i = 0 ; i //创建行

088	for (int j = 0 ; j //写入数据

089

}

090

}

091	newBook .Write (fs ) ;

092	return true ;

093

}

094	catch (Exception err)

095

{

096	throw new Exception ("转换数据到Excel失败："+err.Message ) ;

097

}

098

finally

099

{

100	fs.Close () ;

101

}

102

}

103

///

104	/// 将数据集导出到Excel文件

105

///

106	/// DataTable对象

107	/// Excel文件名称

108

/// 工作簿名称

109	/// 是否转换成功

110	public static bool ConvertToExcel(System.Data.DataTable dt, string xlsSaveFileName,string sheetName)

111

{

112	FileStream fs = new FileStream (xlsSaveFileName, FileMode.Create ) ;

113

try

114

{

115	HSSFWorkbook newBook = new HSSFWorkbook () ;

116	HSSFSheet    newSheet =(HSSFSheet ) newBook.CreateSheet (sheetName ) ;//新建工作簿

117	for (int i = 0 ; i //创建行

118	for (int j = 0 ; j //写入数据

119

}

120

}

121	newBook .Write (fs ) ;

122	return true ;

123

}

124	catch (Exception err)

125

{

126	throw new Exception ("转换数据到Excel失败："+err.Message ) ;

127

}

128

finally

129

{

130	fs.Close () ;

131

}

132

}

133

///

134	/// 将数据集导出到Excel文件

135

///

136	/// DataGridView对象

137	/// Excel文件名称

138

/// 工作簿名称

139	/// 是否转换成功

140	public static bool ConvertToExcel(System.Windows.Forms.DataGridView dgv, string xlsSaveFileName,string sheetName)

141

{

142	return ConvertToExcel((System.Data.DataTable )dgv.DataSource  ,xlsSaveFileName,sheetName )  ;

143	}下面是导入数据到DataTable:

144

145

Code///

146	/// 将数据导出到DataTable中

147

///

148	/// 二维数组数据

149

/// 列名

150	/// DataTable对象

151	public static System.Data.DataTable ConvertToDataTable(T[,] data , string[] columnsName)

152

{

153	System.Data.DataTable dt = new System.Data.DataTable () ;

154	if (data.GetLength (1)>columnsName.Length )

155

{

156	throw new Exception ("列名长度不足");

157

}

158

try

159

{

160

//先添加列名

161	for (int i = 0 ; i new DataColumn (columnsName [i ],typeof (System.String ) ) ;

162	dt.Columns.Add (dc ) ;

163

}

164

//添加数据

165	for (int i = 0 ;i for (int j =0 ; j return dt ;

166

}

167	catch (Exception err)

168

{

169	throw new Exception ("转换数据到DataTable失败："+err.Message ) ;

170

}

171

}

172

///

173	/// 将数据导出到DataTable中

174

///

175	/// 二维数组数据

176	/// DataTable对象

177	public static System.Data.DataTable ConvertToDataTable(T[,] data )

178

{

179	System.Data.DataTable dt = new System.Data.DataTable () ;

180

try

181

{

182

//先添加列名

183	for (int i = 0 ; i new DataColumn() ;

184	dt.Columns.Add (dc ) ;

185

}

186

//添加数据

187	for (int i = 0 ;i for (int j =0 ; j return dt ;

188

}

189	catch (Exception err)

190

{

191	throw new Exception ("转换数据到DataTable失败："+err.Message ) ;

192

}

193

}

194

///

195	/// 将数据导出到DataTable中

196

///

197	/// 交错数组数据

198

/// 列名

199	/// DataTable对象

200	public static System.Data.DataTable ConvertToDataTable(T[][] data , string[] columnsName)

201

{

202	System.Data.DataTable dt = new System.Data.DataTable () ;

203	if (data[0].Length >columnsName.Length )

204

{

205	throw new Exception ("列名长度不足");

206

}

207

try

208

{

209

//先添加列名

210	for (int i = 0 ; i new DataColumn (columnsName [i ],typeof (System.String ) ) ;

211	dt.Columns.Add (dc ) ;

212

}

213

//添加数据

214	for (int i = 0 ;i for (int j =0 ; j return dt ;

215

}

216	catch (Exception err)

217

{

218	throw new Exception ("转换数据到DataTable失败："+err.Message ) ;

219

}

220

}

221

///

222	/// 将数据导出到DataTable中

223

///

224	/// 交错数组数据

225	/// DataTable对象

226	public static System.Data.DataTable ConvertToDataTable(T[][] data)

227

{

228	System.Data.DataTable dt = new System.Data.DataTable () ;

229

try

230

{

231

//先添加列名

232	for (int i = 0 ; i new DataColumn ( ) ;

233	dt.Columns.Add (dc ) ;

234

}

235

//添加数据

236	for (int i = 0 ;i for (int j =0 ; j return dt ;

237

}

238	catch (Exception err)

239

{

240	throw new Exception ("转换数据到DataTable失败："+err.Message ) ;

241

}

242

}

[代码] ASP.NET 程式碼可以生成一個空白的 Excel 檔案，並且添加三個指定名稱的試算表

001	HSSFWorkbook workbook = new HSSFWorkbook();

002	MemoryStream ms = new MemoryStream();

003

004

// 新增試算表。

005	workbook.CreateSheet("試算表 A");

006	workbook.CreateSheet("試算表 B");

007	workbook.CreateSheet("試算表 C");

008

009	workbook.Write(ms);

010	Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));

011	Response.BinaryWrite(ms.ToArray());

012

013	workbook = null;

014	ms.Close();

015	ms.Dispose();

016

017

// 添加数据

018	HSSFWorkbook workbook = new HSSFWorkbook();

019	MemoryStream ms = new MemoryStream();

020

021

// 新增試算表。

022	HSSFSheet sheet = workbook.CreateSheet("My Sheet");

023

// 插入資料值。

024	sheet.CreateRow(0).CreateCell(0).SetCellValue("0");

025	sheet.CreateRow(1).CreateCell(0).SetCellValue("1");

026	sheet.CreateRow(2).CreateCell(0).SetCellValue("2");

027	sheet.CreateRow(3).CreateCell(0).SetCellValue("3");

028	sheet.CreateRow(4).CreateCell(0).SetCellValue("4");

029	sheet.CreateRow(5).CreateCell(0).SetCellValue("5");

030

031	workbook.Write(ms);

032	Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));

033	Response.BinaryWrite(ms.ToArray());

034

035	workbook = null;

036	ms.Close();

037	ms.Dispose();

038	//設定儲存格的背景色

039	HSSFWorkbook workbook = new HSSFWorkbook();

040	MemoryStream ms = new MemoryStream();

041

042

// 新增試算表。

043	HSSFSheet sheet = workbook.CreateSheet("My Sheet");

044	// 建立儲存格樣式。

045	HSSFCellStyle style1 = workbook.CreateCellStyle();

046	style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;

047	style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;

048	HSSFCellStyle style2 = workbook.CreateCellStyle();

049	style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;

050	style2.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;

051	// 設定儲存格樣式與資料。

052	HSSFCell cell = sheet.CreateRow(0).CreateCell(0);

053	cell.CellStyle = style1;

054	cell.SetCellValue(0);

055

056	cell = sheet.CreateRow(1).CreateCell(0);

057	cell.CellStyle = style2;

058	cell.SetCellValue(1);

059

060	cell = sheet.CreateRow(2).CreateCell(0);

061	cell.CellStyle = style1;

062	cell.SetCellValue(2);

063

064	cell = sheet.CreateRow(3).CreateCell(0);

065	cell.CellStyle = style2;

066	cell.SetCellValue(3);

067

068	cell = sheet.CreateRow(4).CreateCell(0);

069	cell.CellStyle = style1;

070	cell.SetCellValue(4);

071

072	workbook.Write(ms);

073	Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));

074	Response.BinaryWrite(ms.ToArray());

075

076	workbook = null;

077	ms.Close();

078	ms.Dispose();

079

080	//上传Excel转换为DataTable

081	if (this.fuUpload.HasFile)

082

{

083	HSSFWorkbook workbook = new HSSFWorkbook(this.fuUpload.FileContent);

084	HSSFSheet sheet = workbook.GetSheetAt(0);

085

086	DataTable table = new DataTable();

087

088	HSSFRow headerRow = sheet.GetRow(0);

089	int cellCount = headerRow.LastCellNum;

090

091	for (int i = headerRow.FirstCellNum; i < cellCount; i++)

092

{

093	DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

094	table.Columns.Add(column);

095

}

096

097	int rowCount = sheet.LastRowNum;

098

099	for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)

100

{

101	HSSFRow row = sheet.GetRow(i);

102	DataRow dataRow = table.NewRow();

103

104	for (int j = row.FirstCellNum; j < cellCount; j++)

105

{

106	if (row.GetCell(j) != null)

107	dataRow[j] = row.GetCell(j).ToString();

108

}

109

110	table.Rows.Add(dataRow);

111

}

112

113	workbook = null;

114	sheet = null;

115

116	this.gvExcel.DataSource = table;

117	this.gvExcel.DataBind();

118

}

[代码] 將 DataTable 和 Excel 檔案間互轉

001	using System;

002	using System.Collections.Generic;

003	using System.Data;

004	using System.IO;

005	using System.Linq;

006	using System.Web;

007	using NPOI;

008	using NPOI.HPSF;

009	using NPOI.HSSF;

010	using NPOI.HSSF.UserModel;

011	using NPOI.POIFS;

012	using NPOI.Util;

013

014	public class DataTableRenderToExcel

015

{

016	public static Stream RenderDataTableToExcel(DataTable SourceTable)

017

{

018	HSSFWorkbook workbook = new HSSFWorkbook();

019	MemoryStream ms = new MemoryStream();

020	HSSFSheet sheet = workbook.CreateSheet();

021	HSSFRow headerRow = sheet.CreateRow(0);

022

023	// handling header.

024	foreach (DataColumn column in SourceTable.Columns)

025	headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

026

027	// handling value.

028	int rowIndex = 1;

029

030	foreach (DataRow row in SourceTable.Rows)

031

{

032	HSSFRow dataRow = sheet.CreateRow(rowIndex);

033

034	foreach (DataColumn column in SourceTable.Columns)

035

{

036	dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

037

}

038

039	rowIndex++;

040

}

041

042	workbook.Write(ms);

043	ms.Flush();

044	ms.Position = 0;

045

046	sheet = null;

047	headerRow = null;

048	workbook = null;

049

050	return ms;

051

}

052

053	public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)

054

{

055	MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;

056	FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);

057	byte[] data = ms.ToArray();

058

059	fs.Write(data, 0, data.Length);

060	fs.Flush();

061	fs.Close();

062

063	data = null;

064	ms = null;

065	fs = null;

066

}

067

068	public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)

069

{

070	HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);

071	HSSFSheet sheet = workbook.GetSheet(SheetName);

072

073	DataTable table = new DataTable();

074

075	HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);

076	int cellCount = headerRow.LastCellNum;

077

078	for (int i = headerRow.FirstCellNum; i < cellCount; i++)

079

{

080	DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

081	table.Columns.Add(column);

082

}

083

084	int rowCount = sheet.LastRowNum;

085

086	for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)

087

{

088	HSSFRow row = sheet.GetRow(i);

089	DataRow dataRow = table.NewRow();

090

091	for (int j = row.FirstCellNum; j < cellCount; j++)

092	dataRow[j] = row.GetCell(j).ToString();

093

}

094

095	ExcelFileStream.Close();

096	workbook = null;

097	sheet = null;

098	return table;

099

}

100

101	public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)

102

{

103	HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);

104	HSSFSheet sheet = workbook.GetSheetAt(SheetIndex);

105

106	DataTable table = new DataTable();

107

108	HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);

109	int cellCount = headerRow.LastCellNum;

110

111	for (int i = headerRow.FirstCellNum; i < cellCount; i++)

112

{

113	DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

114	table.Columns.Add(column);

115

}

116

117	int rowCount = sheet.LastRowNum;

118

119	for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)

120

{

121	HSSFRow row = sheet.GetRow(i);

122	DataRow dataRow = table.NewRow();

123

124	for (int j = row.FirstCellNum; j < cellCount; j++)

125

{

126	if (row.GetCell(j) != null)

127	dataRow[j] = row.GetCell(j).ToString();

128

}

129

130	table.Rows.Add(dataRow);

131

}

132

133	ExcelFileStream.Close();

134	workbook = null;

135	sheet = null;

136	return table;

137

}

138

}

139

140	DataTable table = new DataTable();

141

142	// 填充資料（由讀者自行撰寫）

143

144	// 產生 Excel 資料流。

145	MemoryStream ms = DataTableRenderToExcel.RenderDataTableToExcel(table) as MemoryStream;

146	// 設定強制下載標頭。

147	Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls"));

148

// 輸出檔案。

149	Response.BinaryWrite(ms.ToArray());

150

151	ms.Close();

152	ms.Dispose();

153	//若是要讀取 Excel 檔案並存到 DataTable，只要設定上傳的 Excel 檔案資料流、試算表索引（或名稱）以及起始列索引值即可：

154	if (this.fuUpload.HasFile)

155

{

156	// 讀取 Excel 資料流並轉換成 DataTable。

157	DataTable table = DataTableRenderToExcel.RenderDataTableFromExcel(this.fuUpload.FileContent, 1, 0);

158	this.gvExcel.DataSource = table;

159	this.gvExcel.DataBind();

160

}

[代码] NPOI简单Demo，快速入门代码

01	/// <summary>

02	/// NPOI简单Demo，快速入门代码

03	/// </summary>

04	/// <param name="dtSource"></param>

05	/// <param name="strFileName"></param>

06	/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>

07	/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>

08	public static void ExportEasy(DataTable dtSource, string strFileName)

09

{

10	HSSFWorkbook workbook = new HSSFWorkbook();

11	HSSFSheet sheet = workbook.CreateSheet();

12

13

//填充表头

14	HSSFRow dataRow = sheet.CreateRow(0);

15	foreach (DataColumn column in dtSource.Columns)

16

{

17	dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

18

}

19

20

21

//填充内容

22	for (int i = 0; i < dtSource.Rows.Count; i++)

23

{

24	dataRow = sheet.CreateRow(i + 1);

25	for (int j = 0; j < dtSource.Columns.Count; j++)

26

{

27	dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());

28

}

29

}

30

31

32

//保存

33	using (MemoryStream ms = new MemoryStream())

34

{

35	using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

36

{

37	workbook.Write(fs);

38

}

39

}

40	workbook.Dispose();

41

}

[代码] 获取目录中文件的修改日期，MD5值和文件大小，并存入Excel文件

001	using System;

002	using System.Collections;

003	using System.Collections.Generic;

004	using System.IO;

005	using System.Security.Cryptography;

006	using System.Text;

007	using NPOI.HSSF.UserModel;

008	using NPOI.SS.UserModel;

009

010	namespace GetFileStatusReport

011

{

012	class Program

013

{

014	private const long KB = 1024;

015	private const long MB = 1024 * KB;

016	private const long GB = 1024 * MB;

017	static void Main(string[] args)

018

{

019	HSSFWorkbook workbook = new HSSFWorkbook();

020	HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

021

022

//填充表头

023	HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);

024	dataRow.CreateCell(0).SetCellValue("文件路径");

025	dataRow.CreateCell(1).SetCellValue("修改时间");

026	dataRow.CreateCell(2).SetCellValue("文件大小");

027	dataRow.CreateCell(3).SetCellValue("MD5值");

028	int ii = 0;

029	string filepath = @"D:\eclipse\";

030	DirectoryInfo dir = new DirectoryInfo((filepath.LastIndexOf(Path.DirectorySeparatorChar) == filepath.Length - 1) ? filepath : filepath + Path.DirectorySeparatorChar);

031	FileSystemInfo[] fileArr = dir.GetFileSystemInfos();

032	Queue<FileSystemInfo> Folders = new Queue<FileSystemInfo>(dir.GetFileSystemInfos());

033	CellStyle cellStyle = workbook.CreateCellStyle();

034	cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy年m月d日");

035	while (Folders.Count > 0)

036

{

037	FileSystemInfo atom = Folders.Dequeue();

038	FileInfo f = atom as FileInfo;

039	if (f == null)

040

{

041	DirectoryInfo d = atom as DirectoryInfo;

042

try

043

{

044	foreach (FileSystemInfo fi in d.GetFileSystemInfos())

045	Folders.Enqueue(fi);

046

}

047

catch

048	{ continue; }

049

}

050	else if (f.Extension.ToLower() == ".pdf")

051

{

052	FileStream fs = new FileStream(f.FullName, FileMode.Open, FileAccess.Read);

053	MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();

054	byte[] md5byte = md5.ComputeHash(fs);

055

int i, j;

056	StringBuilder sb = new StringBuilder(16);

057	foreach (byte b in md5byte)

058

{

059	i = Convert.ToInt32(b);

060	j = i >> 4;

061	sb.Append(Convert.ToString(j, 16));

062	j = ((i << 4) & 0x00ff) >> 4;

063	sb.Append(Convert.ToString(j, 16));

064

}

065	dataRow = (HSSFRow)sheet.CreateRow(++ii);

066	dataRow.CreateCell(0).SetCellValue(f.FullName);

067	Cell c=dataRow.CreateCell(1);

068	c.SetCellValue(f.LastWriteTime);

069	c.CellStyle = cellStyle;

070	long filesize = f.Length;

071	string showsize;

072	if (filesize >= GB)

073	showsize = string.Format("{0,7:f2} GB", (double)filesize / GB);

074	else if (filesize >= MB)

075	showsize = string.Format("{0,7:f2} MB", (double)filesize / MB);

076	else if (filesize >= KB)

077	showsize = string.Format("{0,7:f2} KB", (double)filesize / KB);

078	else if (filesize > 1)

079	showsize = string.Format("{0,7:d} Bytes", filesize);

080	else if (filesize == 1)

081	showsize = "1 Byte";

082

else

083	showsize = "0 Bytes";

084	dataRow.CreateCell(2).SetCellValue(showsize);

085	dataRow.CreateCell(3).SetCellValue(sb.ToString());

086

}

087

}

088

089

//保存

090	using (MemoryStream ms = new MemoryStream())

091

{

092	string xlsxname = @"C:\Result";

093	string xlsxfile;

094	int num = 2;

095	if (File.Exists(xlsxfile = xlsxname + ".xlsx"))

096	while (File.Exists(xlsxfile = xlsxname + num++ + ".xlsx")) ;

097	using (FileStream fs = new FileStream(xlsxfile, FileMode.Create, FileAccess.Write))

098

{

099	workbook.Write(fs);

100

}

101

}

102	workbook.Dispose();

103

}

104

}

105

}

[代码] 生成九九乘法表

01	using System;

02	using System.Collections.Generic;

03	using System.Linq;

04	using System.Text;

05	using NPOI.HSSF.UserModel;

06	using System.IO;

07	using NPOI.HPSF;

08

09	namespace TimesTables

10

{

11	public class Program

12

{

13	static HSSFWorkbook hssfworkbook;

14

15	static void Main(string[] args)

16

{

17	InitializeWorkbook();

18

19	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

20	HSSFRow row;

21	HSSFCell cell;

22	for (int rowIndex = 0; rowIndex < 9; rowIndex++)

23

{

24	row = sheet1.CreateRow(rowIndex);

25	for (int colIndex = 0; colIndex <= rowIndex; colIndex++)

26

{

27	cell = row.CreateCell(colIndex);

28	cell.SetCellValue(String.Format("{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1) * (colIndex + 1)));

29

}

30

}

31

32	WriteToFile();

33

}

34

35	static void WriteToFile()

36

{

37	//Write the stream data of workbook to the root directory

38	FileStream file = new FileStream(@"test.xls", FileMode.Create);

39	hssfworkbook.Write(file);

40	file.Close();

41

}

42

43	static void InitializeWorkbook()

44

{

45	hssfworkbook = new HSSFWorkbook();

46

47	//create a entry of DocumentSummaryInformation

48	DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

49	dsi.Company = "NPOI Team";

50	hssfworkbook.DocumentSummaryInformation = dsi;

51

52	//create a entry of SummaryInformation

53	SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

54	si.Subject = "NPOI SDK Example";

55	hssfworkbook.SummaryInformation = si;

56

}

57

}

58

}

[代码] 杨辉三角

01	using System;

02	using System.Collections.Generic;

03	using System.Text;

04	using System.IO;

05	using System.Drawing;

06	using System.Data;

07	using NPOI.HSSF.UserModel;

08	using NPOI.HPSF;

09

10	namespace 杨辉三角

11

{

12	class Program

13

{

14	static void Main(string[] args)

15

{

16	HSSFWorkbook hssfworkbook;

17	//例如创建 HtmlWriter 还能生成一个相应的 html 文件

18	//iTextSharp.text.html.HtmlWriter.GetInstance(doc, new FileStream("c:\\htmlfile.html", FileMode.Create));

19	hssfworkbook = new HSSFWorkbook();

20

21	//create a entry of DocumentSummaryInformation

22	DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

23	dsi.Company = "NPOI Team";

24	hssfworkbook.DocumentSummaryInformation = dsi;

25

26	//create a entry of SummaryInformation

27	SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

28	si.Subject = "NPOI SDK Example";

29	hssfworkbook.SummaryInformation = si;

30	HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("杨辉三角");

31	HSSFRow row;

32	HSSFCell cell;

33

int i, j;

34	int[][] YH = new int[10][];

35	for (i = 0; i < YH.Length; i++)

36

{

37	YH[i] = new int[i + 1];

38	YH[i][0] = 1;

39	YH[i][i] = 1;

40

}

41	for (i = 2; i < YH.Length; i++)

42	for (j = 1; j < YH[i].Length - 1; j++)//这里是length-1因为前面已经对

43	YH[i][j] = YH[i - 1][j - 1] + YH[i - 1][j];//对角线上的给了值的

44	for (int rowIndex = 0; rowIndex < 10; rowIndex++)

45

{

46	row = (HSSFRow)sheet1.CreateRow(rowIndex);

47	for (int colIndex = 0; colIndex <= rowIndex; colIndex++)

48

{

49	cell = (HSSFCell)row.CreateCell(colIndex);

50	cell.SetCellValue(string.Format("{0,-4:d}", YH[rowIndex][colIndex]));

51

}

52

}

53	string name = @"C:\test";

54	string xlsxfile;

55	int num = 2;

56	if (File.Exists(xlsxfile = name + ".xlsx"))

57	while (File.Exists(xlsxfile = name + num++ + ".xlsx")) ;

58	//Write the stream data of workbook to the root directory

59	FileStream file = new FileStream(xlsxfile, FileMode.Create);

60	hssfworkbook.Write(file);

61	file.Close();

62

}

63

}

64

}

[代码] 组合和全排列

001	using System.Collections.Generic;

002	using System.IO;

003	using NPOI.HSSF.UserModel;

004

005	namespace ArrangeCombine

006

{

007	class Program

008

{

009	private static HSSFSheet newSheetC;

010	private static HSSFSheet newSheetA;

011	private static int rowNumber = 0;

012	static void Main(string[] args)

013

{

014	List<string> list = new List<string>();

015	for (int i = 0; i < 8; i++)

016

{

017	list.Add(i.ToString());

018

}

019	HSSFWorkbook newBook = new HSSFWorkbook();

020	newSheetC = (HSSFSheet)newBook.CreateSheet("组合");

021	C(list, 4);

022	newSheetA = (HSSFSheet)newBook.CreateSheet("全排列");

023	A(list, 0, 7);

024	string name = @"C:\AC";

025	string xlsxfile;

026	int num = 2;

027	if (File.Exists(xlsxfile = name + ".xlsx"))

028	while (File.Exists(xlsxfile = name + num++ + ".xlsx")) ;

029	//Write the stream data of workbook to the root directory

030	FileStream file = new FileStream(xlsxfile, FileMode.Create);

031	newBook.Write(file);

032	file.Close();

033

034

}

035	/// <summary>

036	/// 对数组进行全排列

037	/// </summary>

038	/// <param name="lsArray">要进行全排列的数组</param>

039	/// <param name="begin">进行全排列的开始下标</param>

040	/// <param name="end">进行全排列的结束下标</param>

041	static void A(List<string> lsArray, int begin, int end)

042

{

043	if (begin == end)

044

{

045	HSSFRow newRow = (HSSFRow)newSheetA.CreateRow(rowNumber++);//创建行

046	for (int i = 0; i <= end; i++)

047

{

048	HSSFCell cell = (HSSFCell)newRow.CreateCell(i);

049	cell.SetCellValue(lsArray[i]);

050

}

051

}

052	for (int i = begin; i <= end; i++)

053

{

054	Swap(lsArray, begin, i);

055	A(lsArray, begin + 1, end);

056	Swap(lsArray, begin, i);

057

}

058

}

059	/// <summary>

060	/// 对数组进行组合操作，选取selectCount个元素进行组合

061	/// </summary>

062	/// <param name="lsArray">即将进行组合操作的数组</param>

063	/// <param name="selectCount">选取的元素的个数</param>

064	static void C(List<string> lsArray, int selectCount)

065

{

066	int totolcount = lsArray.Count;

067	int[] currectselect = new int[selectCount];

068	int last = selectCount - 1;

069	for (int i = 0; i < selectCount; i++)

070	currectselect[i] = i;

071	int rowIndex = 0;

072	while (true)

073

{

074	HSSFRow newRow = (HSSFRow)newSheetC.CreateRow(rowIndex++);//创建行

075	for (int i = 0; i < selectCount; i++)

076

{

077	HSSFCell cell = (HSSFCell)newRow.CreateCell(i);

078	cell.SetCellValue(lsArray[currectselect[i]]);

079

}

080	if (currectselect[last] < totolcount - 1)

081	currectselect[last]++;

082

else

083

{

084	int pos = last;

085	while (pos > 0 && currectselect[pos - 1] == currectselect[pos] - 1)

086

pos--;

087	if (pos == 0) return;

088	currectselect[pos - 1]++;

089	for (int i = pos; i < selectCount; i++)

090	currectselect[i] = currectselect[i - 1] + 1;

091

}

092

}

093

}

094	/// <summary>

095	/// 交换数组中的下标为x,y的值

096	/// </summary>

097	/// <param name="lsArray">该数组</param>

098	/// <param name="x"></param>

099	/// <param name="y"></param>

100	static void Swap(List<string> lsArray, int x, int y)

101

{

102	string t = lsArray[x];

103	lsArray[x] = lsArray[y];

104	lsArray[y] = t;

105

}

106

}

107

}

[代码] 随机数函数

01	取0-100之前的随机整数，可设置公式为：

02	sheet1.CreateRow(0).CreateCell(0).SetCellFormula("int(RAND()*100)");

03	取10-20之间的随机实数，可设置公式为：

04	sheet1.CreateRow(0).CreateCell(0).SetCellFormula("rand()*(20-10)+10");

05

随机小写字母：

06	sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+97)");

07

随机大写字母：

08	sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+65)")

09

随机大小写字母：

10	sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,65,97))");

[代码] 生成一张工资单

001	using System;

002	using System.Collections.Generic;

003	using System.Linq;

004	using System.Text;

005	using NPOI.HSSF.UserModel;

006	using System.IO;

007	using NPOI.HPSF;

008	using NPOI.HSSF.Util;

009	using System.Data;

010

011	namespace Payroll

012

{

013	public class Program

014

{

015	static HSSFWorkbook hssfworkbook;

016

017	static void Main(string[] args)

018

{

019	InitializeWorkbook();

020

021

//写标题文本

022	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

023	HSSFCell cellTitle = sheet1.CreateRow(0).CreateCell(0);

024	cellTitle.SetCellValue("XXX公司2009年10月工资单");

025

026

//设置标题行样式

027	HSSFCellStyle style = hssfworkbook.CreateCellStyle();

028	style.Alignment = HSSFCellStyle.ALIGN_CENTER;

029	HSSFFont font = hssfworkbook.CreateFont();

030	font.FontHeight = 20 * 20;

031	style.SetFont(font);

032

033	cellTitle.CellStyle = style;

034

035

//合并标题行

036	sheet1.AddMergedRegion(new Region(0, 0, 1, 6));

037

038	DataTable dt=GetData();

039	HSSFRow row;

040	HSSFCell cell;

041	HSSFCellStyle celStyle=getCellStyle();

042

043	HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

044	HSSFClientAnchor anchor;

045	HSSFSimpleShape line;

046	int rowIndex;

047	for (int i = 0; i < dt.Rows.Count; i++)

048

{

049

//表头数据

050	rowIndex = 3 * (i + 1);

051	row = sheet1.CreateRow(rowIndex);

052

053	cell = row.CreateCell(0);

054	cell.SetCellValue("姓名");

055	cell.CellStyle = celStyle;

056

057	cell = row.CreateCell(1);

058	cell.SetCellValue("基本工资");

059	cell.CellStyle = celStyle;

060

061	cell = row.CreateCell(2);

062	cell.SetCellValue("住房公积金");

063	cell.CellStyle = celStyle;

064

065	cell = row.CreateCell(3);

066	cell.SetCellValue("绩效奖金");

067	cell.CellStyle = celStyle;

068

069	cell = row.CreateCell(4);

070	cell.SetCellValue("社保扣款");

071	cell.CellStyle = celStyle;

072

073	cell = row.CreateCell(5);

074	cell.SetCellValue("代扣个税");

075	cell.CellStyle = celStyle;

076

077	cell = row.CreateCell(6);

078	cell.SetCellValue("实发工资");

079	cell.CellStyle = celStyle;

080

081

082	DataRow dr = dt.Rows[i];

083	//设置值和计算公式

084	row = sheet1.CreateRow(rowIndex + 1);

085	cell = row.CreateCell(0);

086	cell.SetCellValue(dr["FName"].ToString());

087	cell.CellStyle = celStyle;

088

089	cell = row.CreateCell(1);

090	cell.SetCellValue((double)dr["FBasicSalary"]);

091	cell.CellStyle = celStyle;

092

093	cell = row.CreateCell(2);

094	cell.SetCellValue((double)dr["FAccumulationFund"]);

095	cell.CellStyle = celStyle;

096

097	cell = row.CreateCell(3);

098	cell.SetCellValue((double)dr["FBonus"]);

099	cell.CellStyle = celStyle;

100

101	cell = row.CreateCell(4);

102	cell.SetCellFormula(String.Format("$B{0}*0.08",rowIndex+2));

103	cell.CellStyle = celStyle;

104

105	cell = row.CreateCell(5);

106	cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})*0.1",rowIndex+2));

107	cell.CellStyle = celStyle;

108

109	cell = row.CreateCell(6);

110	cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));

111	cell.CellStyle = celStyle;

112

113

114

//绘制分隔线

115	sheet1.AddMergedRegion(new Region(rowIndex+2, 0, rowIndex+2, 6));

116	anchor = new HSSFClientAnchor(0, 125, 1023, 125, 0, rowIndex + 2, 6, rowIndex + 2);

117	line = patriarch.CreateSimpleShape(anchor);

118	line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;

119	line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;

120

121

}

122

123	WriteToFile();

124

}

125

126	static DataTable GetData()

127

{

128	DataTable dt = new DataTable();

129	dt.Columns.Add("FName",typeof(System.String));

130	dt.Columns.Add("FBasicSalary",typeof(System.Double));

131	dt.Columns.Add("FAccumulationFund", typeof(System.Double));

132	dt.Columns.Add("FBonus", typeof(System.Double));

133

134	dt.Rows.Add("令狐冲", 6000, 1000, 2000);

135	dt.Rows.Add("任盈盈", 7000, 1000, 2500);

136	dt.Rows.Add("林平之", 5000, 1000, 1500);

137	dt.Rows.Add("岳灵珊", 4000, 1000, 900);

138	dt.Rows.Add("任我行", 4000, 1000, 800);

139	dt.Rows.Add("风清扬", 9000, 5000, 3000);

140

141	return dt;

142

}

143

144

145	static HSSFCellStyle getCellStyle()

146

{

147	HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();

148	cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;

149	cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;

150	cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN;

151	cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN;

152	return cellStyle;

153

}

154

155	static void WriteToFile()

156

{

157	//Write the stream data of workbook to the root directory

158	FileStream file = new FileStream(@"test.xls", FileMode.Create);

159	hssfworkbook.Write(file);

160	file.Close();

161

}

162

163	static void InitializeWorkbook()

164

{

165	hssfworkbook = new HSSFWorkbook();

166

167	//create a entry of DocumentSummaryInformation

168	DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

169	dsi.Company = "NPOI Team";

170	hssfworkbook.DocumentSummaryInformation = dsi;

171

172	//create a entry of SummaryInformation

173	SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

174	si.Subject = "NPOI SDK Example";

175	hssfworkbook.SummaryInformation = si;

176

}

177

}

178

}

[代码] 插入图片

01	//add picture data to this workbook.

02	byte[] bytes = System.IO.File.ReadAllBytes(@"D:\MyProject\NPOIDemo\ShapeImage\image1.jpg");

03	int pictureIdx = hssfworkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);

04

05	//create sheet

06	HSSFSheet sheet = hssfworkbook.CreateSheet("Sheet1");

07

08	// Create the drawing patriarch.  This is the top level container for all shapes.

09	HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch();

10

11	//add a picture

12	HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);

13	HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

14	pict.Resize();

[代码] 画Grid

01	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

02	HSSFRow row = sheet1.CreateRow(2);

03	row.CreateCell(1);

04	row.HeightInPoints = 240;

05	sheet1.SetColumnWidth(2, 9000);

06	int linesCount = 20;

07

08	HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

09	//因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间，所以这里采用比例的方式

10	double xRatio = 1023.0 / (linesCount*10);

11	double yRatio = 255.0 / (linesCount*10);

12

13

//画竖线

14	int x1 = 0;

15	int y1 = 0;

16	int x2 = 0;

17	int y2 = 200;

18	for (int i = 0; i < linesCount; i++)

19

{

20	HSSFClientAnchor a2 = new HSSFClientAnchor();

21	a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),

22	(short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));

23	HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);

24	shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

25

26

x1 += 10;

27

x2 += 10;

28

}

29

30

//画横线

31

x1 = 0;

32

y1 = 0;

33

x2 = 200;

34

y2 = 0;

35	for (int i = 0; i < linesCount; i++)

36

{

37	HSSFClientAnchor a2 = new HSSFClientAnchor();

38	a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),

39	(short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));

40	HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);

41	shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

42

43

y1 += 10;

44

y2 += 10;

45

}

[代码] 画圆形

01	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

02	HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

03	HSSFClientAnchor a1 = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);

04	HSSFSimpleShape rec1 = patriarch.CreateSimpleShape(a1);

05	rec1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_OVAL;

06	rec1.SetFillColor(125, 125, 125);

07	rec1.LineStyle = HSSFShape.LINESTYLE_DASHGEL;

08	rec1.LineWidth = 12700;

09	rec1.SetLineStyleColor(100, 0, 100);

10	WriteToFile();

[代码] 画线

01

/*

02	通常，利用NPOI画图主要有以下几个步骤：

03	1. 创建一个Patriarch；

04	2. 创建一个Anchor，以确定图形的位置；

05	3. 调用Patriarch创建图形；

06	4. 设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。

07	关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：

08	dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；

09	dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；

10	dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；

11	dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；

12	col1：起始单元格列序号，从0开始计算；

13	row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；

14	col2：终止单元格列序号，从0开始计算；

15	row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；

16

*/

17	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

18	HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();

19	HSSFClientAnchor a1 = new HSSFClientAnchor(255, 125, 1023, 150, 0, 0,2, 2);

20	HSSFSimpleShape line1 = patriarch.CreateSimpleShape(a1);

21

22	line1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;

23	line1.LineStyle = HSSFShape.LINESTYLE_SOLID;

24	//在NPOI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

25	line1.LineWidth = 6350;

[代码] 生成的Excel文件中公式的返回值

1	HSSFWorkbook hssfworkbook=new HSSFWorkbook();

2	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

3	HSSFRow row = sheet1.CreateRow(0);

4	row.CreateCell(0).SetCellValue(3);

5	row.CreateCell(1).SetCellValue(4);

6	HSSFCell cell = row.CreateCell(2);

7	cell.SetCellFormula("$A1+$B1");

8	System.Console.WriteLine(cell.NumericCellValue);

[代码] VLOOKUP

1	row9.CreateCell(1).SetCellFormula("VLOOKUP(A9,$A$2:$C$6,3,TRUE)");

[代码] COUNTIF

01	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

02

03	HSSFRow row1 = sheet1.CreateRow(0);

04	row1.CreateCell(0).SetCellValue("姓名");

05	row1.CreateCell(1).SetCellValue("成绩");

06

07	HSSFRow row2 = sheet1.CreateRow(1);

08	row2.CreateCell(0).SetCellValue("令狐冲");

09	row2.CreateCell(1).SetCellValue(85);

10

11	HSSFRow row3 = sheet1.CreateRow(2);

12	row3.CreateCell(0).SetCellValue("任盈盈");

13	row3.CreateCell(1).SetCellValue(90);

14

15	HSSFRow row4 = sheet1.CreateRow(3);

16	row4.CreateCell(0).SetCellValue("任我行");

17	row4.CreateCell(1).SetCellValue(70);

18

19	HSSFRow row5 = sheet1.CreateRow(4);

20	row5.CreateCell(0).SetCellValue("左冷婵");

21	row5.CreateCell(1).SetCellValue(45);

22

23	HSSFRow row6 = sheet1.CreateRow(5);

24	row6.CreateCell(0).SetCellValue("岳不群");

25	row6.CreateCell(1).SetCellValue(50);

26

27	HSSFRow row7 = sheet1.CreateRow(6);

28	row7.CreateCell(0).SetCellValue("合格人数：");

29	row7.CreateCell(1).SetCellFormula("COUNTIF(B2:B6,\">60\")");

[代码] If函数

01	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

02

03	HSSFRow row1 = sheet1.CreateRow(0);

04	row1.CreateCell(0).SetCellValue("姓名");

05	row1.CreateCell(1).SetCellValue("身份证号");

06	row1.CreateCell(2).SetCellValue("性别");

07	row1.CreateCell(3).SetCellValue("语文");

08	row1.CreateCell(4).SetCellValue("是否合格");

09

10	HSSFRow row2 = sheet1.CreateRow(1);

11	row2.CreateCell(0).SetCellValue("令狐冲");

12	row2.CreateCell(1).SetCellValue("420821198808101014");

13	row2.CreateCell(2).SetCellFormula("IF(MOD(MID(B2,18,1),2)=0,\"男\",\"女\")");

14	row2.CreateCell(3).SetCellValue(85);

15	row2.CreateCell(4).SetCellFormula("IF(D2>60,IF(D2>90,\"优秀\",\"合格\"),\"不合格\")");

[代码] SUM函数

01	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

02	HSSFRow row1 = sheet1.CreateRow(0);

03	HSSFCell cel1 = row1.CreateCell(0);

04	HSSFCell cel2 = row1.CreateCell(1);

05	HSSFCell cel3 = row1.CreateCell(2);

06	HSSFCell celSum1 = row1.CreateCell(3);

07	HSSFCell celSum2 = row1.CreateCell(4);

08	HSSFCell celSum3 = row1.CreateCell(5);

09

10	cel1.SetCellValue(1);

11	cel2.SetCellValue(2);

12	cel3.SetCellValue(3);

13	celSum2.SetCellFormula("sum(A1,C1)");

[代码] 设置单元格的宽度和高度

01	在Excel中，单元格的宽度其实就是列的宽度，因为Excel假设这一列的单元格的宽度肯定一致。所以要设置单元格的宽度，我们就得从列的宽度下手，HSSFSheet有个方法叫SetColumnWidth，共有两个参数：一个是列的索引（从0开始），一个是宽度。

02	现在假设你要设置B列的宽度，就可以用下面的代码：

03	HSSFWorkbook hssfworkbook = new HSSFWorkbook();

04	HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

05	sheet1.SetColumnWidth(1, 100 * 256);

06	这里你会发现一个有趣的现象，SetColumnWidth的第二个参数要乘以256，这是怎么回事呢？其实，这个参数的单位是1/256个字符宽度，也就是说，这里是把B列的宽度设置为了100个字符。

07	刚才说的是如何设置，那如何去读取一个列的宽度呢？直接用GetColumnWidth方法，这个方法只有一个参数，那就是列的索引号。如下所示：

08	int col1width = sheet1.GetColumnWidth(1);

09

10	说完宽度，我们来说高度，在Excel中，每一行的高度也是要求一致的，所以设置单元格的高度，其实就是设置行的高度，所以相关的属性也应该在HSSFRow上，它就是HSSFRow.Height和HeightInPoints，这两个属性的区别在于HeightInPoints的单位是点，而Height的单位是1/20个点，所以Height的值永远是HeightInPoints的20倍。

11	要设置第一行的高度，可以用如下代码：

12	sheet1.CreateRow(0).Height = 200*20;

13

或者

14	sheet1.CreateRow(0).HeightInPoints = 200;

15	如果要获得某一行的行高，可以直接拿HSSFRow.Height属性的返回值。

16

17	你可能觉得一行一行设置行高或者一列一列设置列宽很麻烦，那你可以考虑使用HSSFSheet.DefaultColumnWidth、HSSFSheet.DefaultRowHeight和HSSFSheet.DefaultRowHeightInPoints属性。

18	一旦设置了这些属性，如果某一行或者某一列没有设置宽度，就会使用默认宽度或高度。代码如下：

19	sheet1.DefaultColumnWidth=100*256;

20	sheet1.DefaultRowHeight=30*20;