将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; |