C# ExcelHelper 

  1 public class ExcelHelper
  2 {
  3     private Excel.Application oExcel = null;
  4     private Excel.Workbook oBook = null;
  5     private Excel.Worksheet oSheet = null;
  6     private Excel.Range oRange = null;
  7     public ExcelHelper()
  8     {
  9         oExcel = new Excel.Application();
 10         oBook = oExcel.Application.Workbooks.Add(true);
 11         oSheet = (Excel.Worksheet)oBook.ActiveSheet;
 12     }
 13     /// <summary>
 14     /// 向excel写入文本（如向“A1”写入文字：InsertText("A1",要填入的文字)）
 15     /// </summary>
 16     /// <param name="val_range">单元格</param>
 17     /// <param name="val_text">文本</param>
 18     public void InsertText(string val_range, string val_text)
 19     {
 20         oRange = oSheet.Range[val_range];
 21         oRange.Value = val_text;
 22     }
 23     /// <summary>
 24     /// 向excel写入文本
 25     /// </summary>
 26     /// <param name="val_range">单元格</param>
 27     /// <param name="val_text">文本</param>
 28     /// <param name="val_fontSize">字体大小</param>
 29     public void InsertText(string val_range, string val_text, float val_fontSize)
 30     {
 31         oRange = oSheet.Range[val_range];
 32         oRange.Value = val_text;
 33         oRange.Font.Size = val_fontSize;
 34     }
 35     /// <summary>
 36     /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字)）
 37     /// </summary>
 38     /// <param name="val_i">行号</param>
 39     /// <param name="val_j">列号</param>
 40     /// <param name="val_text">文本</param>
 41     public void InsertText(int val_i, int val_j, string val_text)
 42     {
 43         oRange = (Excel.Range)oSheet.Cells[val_i, val_j];
 44         oRange.Value = val_text;
 45     }
 46     /// <summary>
 47     /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字,字体大小)）
 48     /// </summary>
 49     /// <param name="val_i">行号</param>
 50     /// <param name="val_j">列号</param>
 51     /// <param name="val_text">文本</param>
 52     /// <param name="val_fontSize">字体大小</param>
 53     public void InsertText(int val_i, int val_j, string val_text, float val_fontSize)
 54     {
 55         oRange = oSheet.Cells[val_i, val_j];
 56         oRange.Value = val_text;
 57         oRange.Font.Size = val_fontSize;
 58     }
 59     /// <summary>
 60     /// 读取excel文本项（如读取“B1”的文字：ReadText("B1")）
 61     /// </summary>
 62     /// <param name="val_range">单元格</param>
 63     /// <returns></returns>
 64     public string ReadText(string val_range)
 65     {
 66         oRange = oSheet.Range[val_range];
 67         return oRange.Text.ToString();
 68     }
 69     /// <summary>
 70     /// 读取excel文本项（如读取“A1”的文字：ReadText(1,1)）
 71     /// </summary>
 72     /// <param name="val_i">行号</param>
 73     /// <param name="val_j">列号</param>
 74     /// <returns></returns>
 75     public string ReadText(int val_i, int val_j)
 76     {
 77         oRange = oSheet.Cells[val_i, val_j];
 78         return oRange.Text.ToString();
 79     }
 80     /// <summary>
 81     /// 合并单元格
 82     /// </summary>
 83     /// <param name="x1">行号</param>
 84     /// <param name="y1">列号</param>
 85     /// <param name="x2">行号</param>
 86     /// <param name="y2">列号</param>
 87     public void MerMergeCells(int x1, int y1, int x2, int y2)
 88     {
 89         oSheet.Range[oSheet.Cells[x1, y1], oSheet.Cells[x2, y2]].Merge();
 90     }
 91     /// <summary>
 92     /// 设置excel列的默认样式
 93     /// </summary>
 94     public void SetColumnDefaultStyle()
 95     {
 96         oSheet.Columns.EntireColumn.AutoFit();
 97         oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
 98     }
 99     /// <summary>
100     /// 保存excel文件（如要将excel保存到D:\test.xls：SaveAs）
101     /// </summary>
102     /// <param name="val_saveAsFilePath">文件路径</param>
103     /// <returns></returns>
104     public bool SaveAs(string val_saveAsFilePath)
105     {
106         try
107         {
108             oSheet.SaveAs(val_saveAsFilePath);
109             return true;
110         }
111         catch
112         {
113             return false;
114         }
115     }
116     public bool Print()
117     {
118         try
119         {
120             oSheet.PrintOut();
121             return true;
122         }
123         catch
124         {
125             return false;
126         }
127     }
128     /// <summary>
129     /// 自动释放excel资源
130     /// </summary>
131     public void Dispose()
132     {
133         if (oExcel != null)
134         {
135             oExcel.Workbooks.Close();
136             oExcel.Quit();
137             KillAllExcel();
138             oExcel = null;
139         }
140         if (oBook != null)
141         {
142             oBook = null;
143         }
144         if (oSheet != null)
145         {
146             oSheet = null;
147         }
148         if (oRange != null)
149         {
150             oRange = null;
151         }
152         GC.Collect();
153     }
154     /// <summary>
155     /// 获取所有excel进程
156     /// </summary>
157     /// <returns></returns>
158     private static List<Process> GetExcelProcesses()
159     {
160         Process[] processes = Process.GetProcesses();
161         List<Process> excelProcesses = new List<Process>();
162         for (int i = 0; i < processes.Length; i++)
163         {
164             if (processes[i].ProcessName.ToUpper() == "EXCEL")
165                 excelProcesses.Add(processes[i]);
166         }
167         return excelProcesses;
168     }
169     /// <summary>
170     /// 杀死所有Excel进程
171     /// </summary>
172     private static void KillAllExcel()
173     {
174         List<Process> excelProcesses = GetExcelProcesses();
175         for (int i = 0; i < excelProcesses.Count; i++)
176         {
177             excelProcesses[i].Kill();
178         }
179     }
180     /// <summary>
181     /// DataTable导出Excel
182     /// </summary>
183     /// <param name="sFileName"></param>
184     /// <param name="dt"></param>
185     public void DataTableToExcel(string fileName, DataTable dt)
186     {
187         int CurrentCol = 0;//当前列
188         int RowCount = dt.Rows.Count + 1;//总行数
189         int ColCount = dt.Columns.Count;//总列数
190         StreamWriter sw = new StreamWriter(fileName, false);//文件如果存在，则自动覆盖
191         try
192         {
193             #region XML头部
194             sw.WriteLine("<?xml version=\"1.0\"?>");
195             sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
196             sw.WriteLine("<Workbook");
197             sw.WriteLine("xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
198             sw.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
199             sw.WriteLine("\t<Styles>");
200             sw.WriteLine("\t\t<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Center\"/><Font ss:FontName=\"宋体\" ss:Size=\"12\"/></Style>");
201             sw.WriteLine("\t\t<Style ss:ID=\"s47\"><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#000000\"/><Interior ss:Color=\"#EBF1DE\" ss:Pattern=\"Solid\"/></Style>");
202             sw.WriteLine("\t\t<Style ss:ID=\"s33\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/></Borders><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#FFFFFF\" ss:Bold=\"1\"/><Interior ss:Color=\"#A5A5A5\" ss:Pattern=\"Solid\"/></Style>");
203             sw.WriteLine("\t\t<Style ss:ID=\"s68\" ss:Parent=\"s33\"><Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/></Style>");
204             sw.WriteLine("\t\t<Style ss:ID=\"s93\" ss:Parent=\"s47\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/></Borders></Style>");
205             sw.WriteLine("\t</Styles>");
206             sw.WriteLine("\t<Worksheet ss:Name=\"Sheet1\">");
207             sw.WriteLine("\t\t<Table ss:DefaultColumnWidth=\"150\" ss:DefaultRowHeight=\"20\">");
208             #endregion
209 
210             #region excel标题
211             sw.WriteLine("\t\t\t<Row>");
212             sw.WriteLine("\t\t\t\t<Cell ss:MergeAcross=\"{0}\" ss:StyleID=\"s68\">",ColCount-1);
213             sw.WriteLine("\t\t\t\t\t<Data ss:Type=\"String\">{0}</Data>",dt.TableName);
214             sw.WriteLine("\t\t\t\t</Cell>");
215             sw.WriteLine("\t\t\t</Row>");
216             #endregion
217 
218             #region excel表头信息
219             sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">");
220             for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++)
221             {
222                 sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">{0}</Data></Cell>", dt.Columns[CurrentCol].ColumnName.ToString().Trim());
223             }
224             sw.WriteLine("\t\t\t</Row>");
225             #endregion
226 
227             #region excel表格内容
228             foreach (DataRow row in dt.Rows)
229             {
230                 sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">");
231                 for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++)
232                 {
233                     sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">");
234                     if (row[CurrentCol] != null)
235                     {
236                         sw.Write(row[CurrentCol].ToString().Trim());
237                     }
238                     else
239                     {
240                         sw.Write("");
241                     }
242                     sw.Write("</Data></Cell>");
243                 }
244                 sw.WriteLine("\t\t\t</Row>");
245             }
246             #endregion
247 
248             #region XML尾部
249             sw.WriteLine("\t\t</Table>");
250             sw.WriteLine("\t</Worksheet>");
251             sw.WriteLine("</Workbook>");
252             #endregion
253         }
254         catch
255         { }
256         finally
257         {
258             sw.Close();
259             sw = null;
260         }
261     }
262 }
View Code

/*导入Excel数据*/

 1 public void ImportExcel(string path)
 2 {
 3   string strConn="";
 4   if(path.IndexOf(".xlsx")>0)
 5   {
 6     strConn="Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES'";
 7   }
 8   else if(path.IndexOf(".xls") > 0 && path.EndsWith("xls"))
 9   {
10     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES'";
11   }
12   OleDbConnection conn = new OleDbConnection(strConn);
13   try
14   {
15     if(conn.State==ConnectionState.Closed)
16     {
17       conn.Open();
18     }
19     string strExcel="select * from [sheet1$]";
20     OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
21     DataSet ds = new DataSet();
22     adapter.Fill(ds, "table1");
23     conn.Close();
24     conn.Dispose();
25   }
26   catch
27   {
28   }
29 }
View Code

问题1：未在本地计算机上注册microsoft.ACE.oledb.12.0提供程序。

原因：由于使用了Access2007版本建立的数据库，但服务器中没有相配合使用的程序，所以出错。

解决方法：http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe 下载，然后安装。

问题2：安装了AccessDatabaseEngine.exe后，仍然报问题1中的错误。

解决方法：选择该应用程序的应用程序池--->选择高级设置--->启用32位应用程序--->true

 

posted @ 2013-05-16 13:54  飛雲若雪  阅读(2148)  评论(0)  编辑  收藏  举报