using
System;
using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Linq;
6 using System.Web;
7 using NPOI;
8 using NPOI.HPSF;
9 using NPOI.HSSF;
10 using NPOI.HSSF.UserModel;
11 using NPOI.POIFS;
12 using NPOI.Util;
13
14 public class DataTableRenderToExcel
15 {
16 public static Stream RenderDataTableToExcel(DataTable SourceTable)
17 {
18 HSSFWorkbook workbook = new HSSFWorkbook();
19 MemoryStream ms = new MemoryStream();
20 HSSFSheet sheet = workbook.CreateSheet();
21 HSSFRow headerRow = sheet.CreateRow( 0 );
22
23 // handling header.
24 foreach (DataColumn column in SourceTable.Columns)
25 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
26
27 // handling value.
28 int rowIndex = 1 ;
29
30 foreach (DataRow row in SourceTable.Rows)
31 {
32 HSSFRow dataRow = sheet.CreateRow(rowIndex);
33
34 foreach (DataColumn column in SourceTable.Columns)
35 {
36 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
37 }
38
39 rowIndex ++ ;
40 }
41
42 workbook.Write(ms);
43 ms.Flush();
44 ms.Position = 0 ;
45
46 sheet = null ;
47 headerRow = null ;
48 workbook = null ;
49
50 return ms;
51 }
52
53 public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
54 {
55 MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
56 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
57 byte [] data = ms.ToArray();
58
59 fs.Write(data, 0 , data.Length);
60 fs.Flush();
61 fs.Close();
62
63 data = null ;
64 ms = null ;
65 fs = null ;
66 }
67
68 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
69 {
70 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
71 HSSFSheet sheet = workbook.GetSheet(SheetName);
72
73 DataTable table = new DataTable();
74
75 HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
76 int cellCount = headerRow.LastCellNum;
77
78 for ( int i = headerRow.FirstCellNum; i < cellCount; i ++ )
79 {
80 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
81 table.Columns.Add(column);
82 }
83
84 int rowCount = sheet.LastRowNum;
85
86 for ( int i = (sheet.FirstRowNum + 1 ); i < sheet.LastRowNum; i ++ )
87 {
88 HSSFRow row = sheet.GetRow(i);
89 DataRow dataRow = table.NewRow();
90
91 for ( int j = row.FirstCellNum; j < cellCount; j ++ )
92 dataRow[j] = row.GetCell(j).ToString();
93 }
94
95 ExcelFileStream.Close();
96 workbook = null ;
97 sheet = null ;
98 return table;
99 }
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 /// <summary> 读取excel
140 /// 默认第一行为标头
141 /// </summary>
142 /// <param name="path"> excel文档路径 </param>
143 /// <returns></returns>
144 public static DataTable RenderDataTableFromExcel( string path) {
145 DataTable dt = new DataTable();
146
147 HSSFWorkbook hssfworkbook;
148 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
149 {
150 hssfworkbook = new HSSFWorkbook(file);
151 }
152 HSSFSheet sheet = hssfworkbook.GetSheetAt( 0 );
153 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
154
155 HSSFRow headerRow = sheet.GetRow( 0 );
156 int cellCount = headerRow.LastCellNum;
157
158 for ( int j = 0 ; j < cellCount; j ++ )
159 {
160 HSSFCell cell = headerRow.GetCell(j);
161 dt.Columns.Add(cell.ToString());
162 }
163
164 for ( int i = (sheet.FirstRowNum + 1 ); i <= sheet.LastRowNum; i ++ )
165 {
166 HSSFRow row = sheet.GetRow(i);
167 DataRow dataRow = dt.NewRow();
168
169 for ( int j = row.FirstCellNum; j < cellCount; j ++ )
170 {
171 if (row.GetCell(j) != null )
172 dataRow[j] = row.GetCell(j).ToString();
173 }
174
dt.Rows.Add(dataRow);
}
return dt;
}
}
using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Linq;
6 using System.Web;
7 using NPOI;
8 using NPOI.HPSF;
9 using NPOI.HSSF;
10 using NPOI.HSSF.UserModel;
11 using NPOI.POIFS;
12 using NPOI.Util;
13
14 public class DataTableRenderToExcel
15 {
16 public static Stream RenderDataTableToExcel(DataTable SourceTable)
17 {
18 HSSFWorkbook workbook = new HSSFWorkbook();
19 MemoryStream ms = new MemoryStream();
20 HSSFSheet sheet = workbook.CreateSheet();
21 HSSFRow headerRow = sheet.CreateRow( 0 );
22
23 // handling header.
24 foreach (DataColumn column in SourceTable.Columns)
25 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
26
27 // handling value.
28 int rowIndex = 1 ;
29
30 foreach (DataRow row in SourceTable.Rows)
31 {
32 HSSFRow dataRow = sheet.CreateRow(rowIndex);
33
34 foreach (DataColumn column in SourceTable.Columns)
35 {
36 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
37 }
38
39 rowIndex ++ ;
40 }
41
42 workbook.Write(ms);
43 ms.Flush();
44 ms.Position = 0 ;
45
46 sheet = null ;
47 headerRow = null ;
48 workbook = null ;
49
50 return ms;
51 }
52
53 public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
54 {
55 MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
56 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
57 byte [] data = ms.ToArray();
58
59 fs.Write(data, 0 , data.Length);
60 fs.Flush();
61 fs.Close();
62
63 data = null ;
64 ms = null ;
65 fs = null ;
66 }
67
68 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
69 {
70 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
71 HSSFSheet sheet = workbook.GetSheet(SheetName);
72
73 DataTable table = new DataTable();
74
75 HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
76 int cellCount = headerRow.LastCellNum;
77
78 for ( int i = headerRow.FirstCellNum; i < cellCount; i ++ )
79 {
80 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
81 table.Columns.Add(column);
82 }
83
84 int rowCount = sheet.LastRowNum;
85
86 for ( int i = (sheet.FirstRowNum + 1 ); i < sheet.LastRowNum; i ++ )
87 {
88 HSSFRow row = sheet.GetRow(i);
89 DataRow dataRow = table.NewRow();
90
91 for ( int j = row.FirstCellNum; j < cellCount; j ++ )
92 dataRow[j] = row.GetCell(j).ToString();
93 }
94
95 ExcelFileStream.Close();
96 workbook = null ;
97 sheet = null ;
98 return table;
99 }
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 /// <summary> 读取excel
140 /// 默认第一行为标头
141 /// </summary>
142 /// <param name="path"> excel文档路径 </param>
143 /// <returns></returns>
144 public static DataTable RenderDataTableFromExcel( string path) {
145 DataTable dt = new DataTable();
146
147 HSSFWorkbook hssfworkbook;
148 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
149 {
150 hssfworkbook = new HSSFWorkbook(file);
151 }
152 HSSFSheet sheet = hssfworkbook.GetSheetAt( 0 );
153 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
154
155 HSSFRow headerRow = sheet.GetRow( 0 );
156 int cellCount = headerRow.LastCellNum;
157
158 for ( int j = 0 ; j < cellCount; j ++ )
159 {
160 HSSFCell cell = headerRow.GetCell(j);
161 dt.Columns.Add(cell.ToString());
162 }
163
164 for ( int i = (sheet.FirstRowNum + 1 ); i <= sheet.LastRowNum; i ++ )
165 {
166 HSSFRow row = sheet.GetRow(i);
167 DataRow dataRow = dt.NewRow();
168
169 for ( int j = row.FirstCellNum; j < cellCount; j ++ )
170 {
171 if (row.GetCell(j) != null )
172 dataRow[j] = row.GetCell(j).ToString();
173 }
174
dt.Rows.Add(dataRow);
}
return dt;
}
}