C# 操作Excel多个sheet的具体的操作是什么呢？让我们来看看下面的实例实现：
复制
private void DataViewExcelBySheetMultipleDt( DataSet ds, string fileName) { try { int sheetCount = ds.Tables.Count; GC.Collect(); Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); int rowIndex = 0; int colIndex = 0; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { rowIndex = 1; colIndex = 1; xSt = (_Worksheet)xBk.Worksheets.Add( Type.Missing, Type.Missing, 1, Type.Missing); switch (sheetIndex) { case 0: xSt.Name = "test1"; break; case 1: xSt.Name = "test2"; break; case 2: xSt.Name = "test3"; break; case 3: xSt.Name = "test4"; break; } //C# 操作Excel多个sheet的具体的操作foreach (DataColumn col in ds.Tables[sheetIndex].Columns) { xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; excel.Cells[rowIndex, colIndex++] = col.ColumnName; } foreach (DataRow row in ds. Tables[sheetIndex].Rows) { rowIndex++; colIndex = 1; foreach (DataColumn col in ds. Tables[sheetIndex].Columns) { if (col.DataType == System.Type.GetType( "System.DateTime")) { if (!"".Equals(row[col.ColumnName].ToString())) excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName]. ToString())).ToString("MM/dd/yyyy"); else excel.Cells[rowIndex, colIndex] = ""; } else if (col.DataType == S ystem.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } colIndex++; } //C# 操作Excel多个sheet的具体的操作} Range allDataWithTitleRange = xSt.get_Range( excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); allDataWithTitleRange.Borders.LineStyle = 1; } string exportDir = "~/Attachment/"; string absFileName = HttpContext. Current.Server.MapPath( System.IO.Path.Combine(exportDir, fileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices. Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices. Marshal.ReleaseComObject(excel); System.Runtime.InteropServices. Marshal.ReleaseComObject(xSt); //C# 操作Excel多个sheet的具体的操作xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception ex) { } } private void DataViewExcelBySheetMultipleDt( DataSet ds, string fileName) { try { int sheetCount = ds.Tables.Count; GC.Collect(); Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //C# 操作Excel多个sheet的具体的操作int rowIndex = 0; int colIndex = 0; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { rowIndex = 1; colIndex = 1; xSt = (_Worksheet)xBk.Worksheets.Add( Type.Missing, Type.Missing, 1, Type.Missing); switch (sheetIndex) { case 0: xSt.Name = "test1"; break; case 1: xSt.Name = "test2"; break; case 2: xSt.Name = "test3"; break; case 3: xSt.Name = "test4"; break; } foreach (DataColumn col in ds.Tables[sheetIndex].Columns) { //C# 操作Excel多个sheet的具体的操作xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; excel.Cells[rowIndex, colIndex++] = col.ColumnName; } foreach (DataRow row in ds.Tables[sheetIndex].Rows) { rowIndex++; colIndex = 1; foreach (DataColumn col in ds.Tables[ sheetIndex].Columns) { if (col.DataType == System.Type.GetType( "System.DateTime")) { if (!"".Equals(row[col.ColumnName].ToString())) excel.Cells[rowIndex, colIndex] = ( Convert.ToDateTime(row[col.ColumnName]. ToString())).ToString("MM/dd/yyyy"); else excel.Cells[rowIndex, colIndex] = ""; } else if (col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } colIndex++; } } //C# 操作Excel多个sheet的具体的操作Range allDataWithTitleRange = xSt.get_Range( excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); allDataWithTitleRange.Borders.LineStyle = 1; } string exportDir = "~/Attachment/"; string absFileName = HttpContext.Current.Server. MapPath(System.IO.Path.Combine(exportDir, fileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices. Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices. Marshal.ReleaseComObject(excel); System.Runtime.InteropServices. Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception ex) { } }
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
上面方法,首先形成一个多个DataTable的DataSet,
C# 操作Excel重点还是
1. 生成一个新的xls时,打开方式,总是会提示进程占用
2. 用不同的sheet时一定要命名
3. 使用传入一个datatable时,总是会重写第一个sheet
C# 操作Excel多个sheet的具体的操作实例就向你介绍到这里，希望对你了解和学习C# 操作Excel多个sheet的具体的操作有所帮助。
【编辑推荐】