C#导入导出Excel通用类(SamWang)
1 /****************************************************************** 2 * 创 建 人： SamWang 3 * 创建时间： 2012-3-16 9:59 4 * 描 述： 5 * 导入导出Excel通用类 6 * 版 本： V1.0 7 * 环 境： VS2005 8 ******************************************************************/ 9 using System; 10 using System.Collections.Generic; 11 using System.Text; 12 using System.Windows.Forms; 13 using Excel = Microsoft.Office.Interop.Excel; 14 using System.Data; 15 using System.Drawing; 16 using System.Collections; 17 using System.Diagnostics; 18 using System.Data.OleDb; 19 20 namespace LingDang.CRM.UI.Client 21 { 22 public class ExcelIO:IDisposable 23 { 24 #region Constructors 25 private ExcelIO() 26 { 27 status = IsExistExecl() ? 0 : -1; 28 } 29 30 public static ExcelIO GetInstance() 31 { 32 //if(instance == null) 33 //{ 34 // lock (syncRoot) 35 // { 36 // if(instance == null) 37 // { 38 // instance = new ExcelIO(); 39 // } 40 // } 41 //} 42 //return instance; 43 return new ExcelIO(); 44 } 45 #endregion 46 47 #region Fields 48 private static ExcelIO instance; 49 private static readonly object syncRoot = new object(); 50 private string returnMessage; 51 private Excel.Application xlApp; 52 private Excel.Workbooks workbooks = null; 53 private Excel.Workbook workbook = null; 54 private Excel.Worksheet worksheet = null; 55 private Excel.Range range = null; 56 private int status = -1; 57 private bool disposed = false;//是否已经释放资源的标记 58 #endregion 59 60 #region Properties 61 /// <summary> 62 /// 返回信息 63 /// </summary> 64 public string ReturnMessage 65 { 66 get { return returnMessage; } 67 } 68 69 /// <summary> 70 /// 状态:0-正常，-1-失败 1-成功 71 /// </summary> 72 public int Status 73 { 74 get { return status;} 75 } 76 #endregion 77 78 #region Methods 79 /// <summary> 80 /// 判断是否安装Excel 81 /// </summary> 82 /// <returns></returns> 83 protected bool IsExistExecl() 84 { 85 try 86 { 87 xlApp = new Excel.Application(); 88 if (xlApp == null) 89 { 90 returnMessage = "无法创建Excel对象，可能您的计算机未安装Excel!"; 91 return false; 92 } 93 } 94 catch (Exception ex) 95 { 96 returnMessage = "请正确安装Excel！"; 97 //throw ex; 98 return false; 99 }100 101 return true;102 }103 104 /// <summary>105 /// 获得保存路径106 /// </summary>107 /// <returns></returns>108 public static string SaveFileDialog()109 {110 SaveFileDialog sfd = new SaveFileDialog();111 sfd.DefaultExt = "xls";112 sfd.Filter = "Excel文件(*.xls)|*.xls";113 if (sfd.ShowDialog() == DialogResult.OK)114 {115 return sfd.FileName;116 }117 return string.Empty;118 }119 120 /// <summary>121 /// 获得打开文件的路径122 /// </summary>123 /// <returns></returns>124 public static string OpenFileDialog()125 {126 OpenFileDialog ofd = new OpenFileDialog();127 ofd.DefaultExt = "xls";128 ofd.Filter = "Excel文件(*.xls)|*.xls";129 if (ofd.ShowDialog() == DialogResult.OK)130 {131 return ofd.FileName;132 }133 return string.Empty;134 }135 136 /// <summary>137 /// 设置单元格边框138 /// </summary>139 protected void SetCellsBorderAround()140 {141 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);142 //if (dt.Rows.Count > 0)143 //{144 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;145 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;146 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;147 //}148 //if (dt.Columns.Count > 1)149 {150 range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;151 range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;152 range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;153 }154 }155 156 /// <summary>157 /// 将DataTable导出Excel158 /// </summary>159 /// <param name="dt">数据集</param>160 /// <param name="saveFilePath">保存路径</param>161 /// <param name="reportName">报表名称</param>162 /// <returns>是否成功</returns>163 public bool DataTableToExecl(DataTable dt, string saveFileName, string reportName)164 {165 //判断是否安装Excel166 bool fileSaved = false;167 if(status == -1) return fileSaved;168 //判断数据集是否为null169 if (dt == null)170 {171 returnMessage = "无引出数据！";172 return false;173 }174 //判断保存路径是否有效175 if (!saveFileName.Contains(":"))176 {177 returnMessage = "引出路径有误！请选择正确路径！";178 return false;179 }180 181 //创建excel对象182 workbooks = xlApp.Workbooks;183 workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);184 worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1185 worksheet.Cells.Font.Size = 10;186 worksheet.Cells.NumberFormat = "@";187 long totalCount = dt.Rows.Count;188 long rowRead = 0;189 float percent = 0;190 int rowIndex = 0;191 192 //第一行为报表名称，如果为null则不保存该行 193 ++rowIndex;194 worksheet.Cells[rowIndex, 1] = reportName;195 range = (Excel.Range)worksheet.Cells[rowIndex, 1];196 range.Font.Bold = true;197 198 //写入字段(标题)199 ++rowIndex;200 for (int i = 0; i < dt.Columns.Count; i++)201 {202 worksheet.Cells[rowIndex,i+1] = dt.Columns[i].ColumnName;203 range = (Excel.Range)worksheet.Cells[rowIndex, i + 1];204 205 range.Font.Color = ColorTranslator.ToOle(Color.Blue);206 range.Interior.Color = dt.Columns[i].Caption == "表体" ? ColorTranslator.ToOle(Color.SkyBlue) : ColorTranslator.ToOle(Color.Yellow);207 }208 209 //写入数据210 ++rowIndex;211 for (int r = 0; r < dt.Rows.Count; r++)212 {213 for (int i = 0; i < dt.Columns.Count; i++)214 {215 worksheet.Cells[r + rowIndex, i + 1] = dt.Rows[r][i].ToString();216 }217 rowRead++;218 percent = ((float)(100 * rowRead)) / totalCount;219 }220 221 //画单元格边框222 range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);223 this.SetCellsBorderAround();224 225 //列宽自适应226 range.EntireColumn.AutoFit();227 228 //保存文件229 if (saveFileName != "")230 {231 try232 {233 workbook.Saved = true;234 workbook.SaveCopyAs(saveFileName);235 fileSaved = true;236 }237 catch (Exception ex)238 {239 fileSaved = false;240 returnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;241 }242 }243 else244 {245 fileSaved = false;246 }247 248 //释放Excel对应的对象（除xlApp,因为创建xlApp很花时间，所以等析构时才删除)249 //Dispose(false);250 Dispose();251 return fileSaved;252 }253 254 /// <summary>255 /// 导入EXCEL到DataSet256 /// </summary>257 /// <param name="fileName">Excel全路径文件名</param>258 /// <returns>导入成功的DataSet</returns>259 public DataSet ImportExcel(string fileName)260 {261 if (status == -1) return null;262 //判断文件是否被其他进程使用 263 try264 {265 workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);266 worksheet = (Excel.Worksheet)workbook.Worksheets[1];267 }268 catch269 {270 returnMessage = "Excel文件处于打开状态，请保存关闭";271 return null;272 }273 274 //获得所有Sheet名称275 int n = workbook.Worksheets.Count;276 string[] sheetSet = new string[n];277 ArrayList al = new ArrayList();278 for (int i = 0; i < n; i++)279 {280 sheetSet[i] = ((Excel.Worksheet)workbook.Worksheets[i+1]).Name;281 }282 283 //释放Excel相关对象284 Dispose();285 286 //把EXCEL导入到DataSet287 DataSet ds = null;288 //string connStr = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\""; 289 List<string> connStrs = new List<string>(); 290 connStrs.Add("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"");291 connStrs.Add("Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"");292 foreach (string connStr in connStrs)293 {294 ds = GetDataSet(connStr, sheetSet);295 if (ds != null) break;296 } 297 return ds;298 }299 300 /// <summary>301 /// 通过olddb获得dataset302 /// </summary>303 /// <param name="connectionstring"></param>304 /// <returns></returns>305 protected DataSet GetDataSet(string connStr, string[] sheetSet)306 {307 DataSet ds = null;308 using (OleDbConnection conn = new OleDbConnection(connStr))309 {310 try311 {312 conn.Open();313 OleDbDataAdapter da;314 ds = new DataSet();315 for (int i = 0; i < sheetSet.Length; i++)316 {317 string sql = "select * from [" + sheetSet[i] + "$] ";318 da = new OleDbDataAdapter(sql, conn);319 da.Fill(ds, sheetSet[i]);320 da.Dispose();321 }322 conn.Close();323 conn.Dispose();324 }325 catch (Exception ex)326 {327 return null;328 } 329 }330 return ds;331 }332 333 /// <summary>334 /// 释放Excel对应的对象资源335 /// </summary>336 /// <param name="isDisposeAll"></param>337 protected virtual void Dispose(bool disposing)338 {339 try340 {341 if (!disposed)342 {343 if (disposing)344 {345 if (range != null)346 {347 System.Runtime.InteropServices.Marshal.ReleaseComObject(range);348 range = null;349 }350 if (worksheet != null)351 {352 System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);353 worksheet = null;354 }355 if (workbook != null)356 {357 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);358 workbook = null;359 }360 if (workbooks != null)361 {362 xlApp.Application.Workbooks.Close();363 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);364 workbooks = null;365 }366 if (xlApp != null)367 {368 xlApp.Quit();369 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);370 }371 int generation = GC.GetGeneration(xlApp);372 System.GC.Collect(generation);373 }374 375 //非托管资源的释放376 //KillExcel();377 }378 disposed = true;379 }380 catch (Exception e)381 {382 throw e;383 } 384 }385 386 /// <summary> 387 /// 会自动释放非托管的该类实例的相关资源388 /// </summary>389 public void Dispose()390 {391 try392 {393 Dispose(true);394 //告诉垃圾回收器,资源已经被回收395 GC.SuppressFinalize(this);396 }397 catch (Exception e)398 {399 throw e;400 }401 }402 403 /// <summary>404 /// 关闭405 /// </summary>406 public void Close()407 {408 try409 {410 this.Dispose();411 }412 catch (Exception e)413 {414 415 throw e;416 }417 }418 419 /// <summary>420 /// 析构函数421 /// </summary>422 ~ExcelIO()423 {424 try425 {426 Dispose(false);427 }428 catch (Exception e)429 {430 throw e;431 }432 }433 434 /// <summary>435 /// 关闭Execl进程(非托管资源使用)436 /// </summary>437 private void KillExcel()438 {439 try440 {441 Process[] ps = Process.GetProcesses();442 foreach (Process p in ps)443 {444 if (p.ProcessName.ToLower().Equals("excel"))445 {446 //if (p.Id == ExcelID)447 {448 p.Kill();449 }450 }451 }452 }453 catch (Exception ex)454 {455 //MessageBox.Show("ERROR " + ex.Message);456 }457 }458 459 #endregion460 461 #region Events462 463 #endregion 464 465 466 #region IDisposable 成员467 468 469 #endregion470 }471 }
作者 SamWangTAG:
