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 }
/*导入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 }
问题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
