excel导入时还要保存字体、其背景颜色等信息时读取方法就要改变：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.IO;
6 using System.Data.OleDb;
7 using System.Data;
8 using Microsoft.Office.Interop.Excel;
9 using System.Reflection;
10 using System.Runtime.InteropServices;
11
12 namespace WinOrderAd
13 {
14 public class Excel
15 {
16 public string FilePath
17 {
18 get;
19 set;
20 }
21 public Dictionary<string, string> FiledNames
22 {
23 get;
24 set;
25 }
26 public Excel()
27 {
28 }
29
30 public DataSet ImportExcel()//若只需要知道数据就用此方法
31 {
32
33 try
34 {
35 string strConn;
36 if (Path.GetExtension(FilePath) == ".xlsx")
37 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
38 else
39 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
40 OleDbConnection OleConn = new OleDbConnection(strConn);
41 OleConn.Open();
42 System.Data.DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
43 DataSet OleDsExcle = new DataSet();
44 for (int i = 0; i < table.Rows.Count; i++)
45 {
46 string tableName = table.Rows[i]["Table_Name"].ToString();
47 tableName = tableName.Replace("'", "");
48 if (tableName.EndsWith("$"))
49 {
50 string sql = "SELECT * FROM [" + tableName + "]";
51 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
52 OleDaExcel.Fill(OleDsExcle, tableName);
53 OleConn.Close();
54 }
55 }
56 return OleDsExcle;
57 }
58 catch (Exception err)
59 {
60 throw err;
61 }
62 }
63
64
65 /// <summary>
66 /// 用Excel Com组件方式读取Excel内容到DataSet(兼容性较高)
67 /// </summary>
68 /// <param name="path"></param>
69 /// <returns></returns>
70 public DataSet ToDataTableEx()
71 {
72 Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
73
74 excel.Visible = false;
75 excel.ScreenUpdating = false;
76 excel.DisplayAlerts = false;
77
78 excel.Workbooks.Add(FilePath);
79 Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
80 Range rangecell = null;
81 DataSet ds = new DataSet();
82 try
83 {
84 //遍历Worksheets中的每张表
85 for (int i = 1; i <= excel.Worksheets.Count; i++)
86 {
87 //获得指定表
88 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[i];
89
90 System.Data.DataTable dt = new System.Data.DataTable();
95
96 //取表明赋值到dt TableName
97 dt.TableName = worksheet.Name;
98
99 worksheet.Columns.EntireColumn.AutoFit();
100
101 int row = worksheet.UsedRange.Rows.Count;
102 int col = worksheet.UsedRange.Columns.Count;
103
104 for (int c = 1; c <= col; c++)
105 {
106 dt.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text));
107 }
108 //添加一样式列
109 dt.Columns.Add(new DataColumn("Style"));
110
111 for (int r = 2; r <= row; r++)
112 {
113 DataRow newRow = dt.NewRow();
114 for (int c = 1; c <= col; c++)
115 {
116 rangecell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c];
117 newRow[c - 1] = rangecell.Text;
118 if (c == 3)
119 {
120 //取信息的字体颜色与背景颜色
121 newRow[col] = rangecell.Font.Color + "|" + rangecell.Interior.Color;
122 }
123 }
124 dt.Rows.Add(newRow);
125 }
126 ds.Tables.Add(dt);
127 }
128 }
129 catch (Exception ex)
130 {
131 throw (ex);
132 }
133 finally
134 {
135 if (worksheet != null)
136 {
137 System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
138 worksheet = null;
139 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangecell);
140 rangecell = null;
141 }
142 excel.Workbooks.Close();
143 excel.Quit();
144 int generation = System.GC.GetGeneration(excel);
145 if (excel != null)
146 {
147 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
148 excel = null;
149 }
150 System.GC.Collect(generation);
151 }
152 return ds;
153 }
154
341
342 }
343 }
导出设置其样式
1 public void ExportExcel(string[] listTableName, string[] listColName, List<List<Ad>> resource, string exporFilePath)
2 {
3 List<Ad> list = null;
4 List<Ad> listresource = null;
5 Microsoft.Office.Interop.Excel.Application app =
6 new Microsoft.Office.Interop.Excel.ApplicationClass();
7
8 app.Visible = false;
9 app.ScreenUpdating = false;
10 app.DisplayAlerts = false;
11 Workbook wBook = app.Workbooks.Add(true);
12
13 InsertLinkWorksheet(app, wBook);
14
15 Worksheet wSheet = null;
16 Range rangeResource = null;
17 Range rangeContent = null;
18 Range rangeTitle = null;
19 //for (int k = 0; k < listList.Count; k++)
20 for (int k = listList.Count - 1; k >= 0; k--)
21 { 23 listresource = resource[k];//已排好的数据
24 wSheet = wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
25 wSheet.Name = listTableName[k];
26
27 try
28 {
29 int resourcenum = listresource.Count;
30 if (resourcenum > 0)
31 {
32 for (int i = 0; i < resourcenum; i++)
33 {
34 wSheet.Cells[i + 2, 1] = (i + 1);
35 wSheet.Cells[i + 2, 2] = listresource[i].ID;
36 wSheet.Cells[i + 2, 3] = listresource[i].Info;
37 wSheet.Cells[i + 2, 4] = listresource[i].Format;
38 wSheet.Cells[i + 2, 5] = listresource[i].Times;
39 wSheet.Cells[i + 2, 6] = listresource[i].Attach;
40 wSheet.Cells[i + 2, 7] = listresource[i].Frequency;
41 wSheet.Cells[i + 2, 8] = listresource[i].Same;
42 wSheet.Cells[i + 2, 9] = listresource[i].Dif;
43 wSheet.Cells[i + 2, 10] = listresource[i].Balanced;
44
45 if (listresource[i].RowStyle.Split('|')[0] != "")
46 {
47 rangeResource = wSheet.get_Range(wSheet.Cells[i + 2, 1], wSheet.Cells[i + 2, 10]);
48 rangeResource.Font.Color = listresource[i].RowStyle.Split('|')[0];
49 rangeResource.Interior.Color = listresource[i].RowStyle.Split('|')[1];
50 }
51 }
52 }
53 78 int m = 0;
79 int col = listColName.Count();
80 for (m = 0; m < col; m++)
81 {
82 string headname = listColName[m];//单元格头部
83 wSheet.Cells[1, 1 + m] = headname;
84 }
85 //内容
86 rangeContent = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[list.Count + resourcenum + 2 + 1, col]);
87 rangeContent.Borders.Color = System.Drawing.Color.Black.ToArgb();
88 //rangeContent.Interior.Color = 10092543; //设置区域背景色
89 rangeContent.VerticalAlignment = -4108;//竖向居中
90 rangeContent.HorizontalAlignment = -4108;//横向居中
91 rangeContent.RowHeight = 18;
92 rangeContent.EntireColumn.AutoFit();//自动调整列宽
93 //标题
94 rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, col]);
95 rangeTitle.Borders.Color = System.Drawing.Color.Black.ToArgb();
96 rangeTitle.Interior.Color = 65280; //设置区域背景色
97 rangeTitle.VerticalAlignment = -4108;
98 rangeTitle.HorizontalAlignment = -4108;
99 rangeTitle.RowHeight = 18;
100 rangeTitle.EntireColumn.AutoFit();
101 //冻结首行
102 //rangeTitle.Select();
103 app.ActiveWindow.SplitColumn = 0;
104 app.ActiveWindow.SplitRow = 1;
105 app.ActiveWindow.FreezePanes = true;
106 //rangeTitle.Font.Bold = true; //设置字体粗体。
107 }
108 catch (Exception err)
109 {
110 throw err;
111 }
112 finally
113 {
114
115 }
116 }
117 //设置禁止弹出保存和覆盖的询问提示框
118 app.DisplayAlerts = false;
119 app.AlertBeforeOverwriting = false;
120 ((Worksheet)wBook.Worksheets["Sheet1"]).Delete();
121 try
122 {
123 wBook.Saved = true;
124 //保存工作簿
125 System.Reflection.Missing miss = System.Reflection.Missing.Value;
126 wBook.SaveAs(exporFilePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
127 }
128 catch (Exception ex)
129 {
130 throw ex;
131 }
132
133 if (rangeResource != null)
134 {
135 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeResource);
136 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeContent);
137 System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeTitle);
138 rangeResource = null;
139 rangeContent = null;
140 rangeTitle = null;
141 }
142
143 if (wSheet != null)
144 {
145 System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
146 wSheet = null;
147 }
148
149 if (wBook != null)
150 {
151 System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
152 wBook = null;
153 }
154 app.Workbooks.Close();
155 app.Quit();
156 int generation = System.GC.GetGeneration(app);
157 if (app != null)
158 {
159 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
160 app = null;
161 }
162 GC.Collect(generation);
163 }
listTableName为多个sheet的名称
listColName为每页的列名
resource为sheet数据队列
exporFilePath为要保存的路径
时间： 2024-08-22 15:14:26