using System; using System.Data; using System.Data.OleDb; namespace ZFSoft.Joint {     public class ExcelIO     {         private int _ReturnStatus;         private string _ReturnMessage;         /// <summary>         /// 执行返回状态         /// </summary>         public int ReturnStatus         {             get             {                 return _ReturnStatus;             }         }         /// <summary>         /// 执行返回信息         /// </summary>         public string ReturnMessage         {             get             {                 return _ReturnMessage;             }         }         public ExcelIO()         {         }                   /// <summary>         /// 导入EXCEL到DataSet         /// </summary>         /// <param name="fileName">Excel全路径文件名</param>         /// <returns>导入成功的DataSet</returns>         public DataTable ImportExcel(string fileName)         {             //判断是否安装EXCEL             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();             if (xlApp == null)             {                 _ReturnStatus = -1;                 _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";                 return null;             }             //判断文件是否被其他进程使用                        Microsoft.Office.Interop.Excel.Workbook workbook;             try             {                 workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);             }             catch             {                 _ReturnStatus = -1;                 _ReturnMessage = "Excel文件处于打开状态，请保存关闭";                 return null;             }             //获得所有Sheet名称             int n = workbook.Worksheets.Count;             string[] SheetSet = new string[n];             System.Collections.ArrayList al = new System.Collections.ArrayList();             for (int i = 1; i <= n; i++)             {                 SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;             }             //释放Excel相关对象             workbook.Close(null, null, null);             xlApp.Quit();             if (workbook != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                 workbook = null;             }             if (xlApp != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);                 xlApp = null;             }             GC.Collect();             //把EXCEL导入到DataSet             DataSet ds = new DataSet();             DataTable table = new DataTable();             string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";             using (OleDbConnection conn = new OleDbConnection(connStr))             {                 conn.Open();                 OleDbDataAdapter da;                 string sql = "select * from [" + SheetSet[0] + "$] ";                 da = new OleDbDataAdapter(sql, conn);                 da.Fill(ds, SheetSet[0]);                 da.Dispose();                 table = ds.Tables[0];                 conn.Close();                 conn.Dispose();             }             return table;         }         /// <summary>         /// 把DataTable导出到EXCEL         /// </summary>         /// <param name="reportName">报表名称</param>         /// <param name="dt">数据源表</param>         /// <param name="saveFileName">Excel全路径文件名</param>         /// <returns>导出是否成功</returns>         public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)         {             if (dt == null)             {                 _ReturnStatus = -1;                 _ReturnMessage = "数据集为空！";                 return false;             }             bool fileSaved = false;             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();             if (xlApp == null)             {                 _ReturnStatus = -1;                 _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";                 return false;             }             Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;             Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);             Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1             worksheet.Cells.Font.Size = 10;             Microsoft.Office.Interop.Excel.Range range;             long totalCount = dt.Rows.Count;             long rowRead = 0;             float percent = 0;             worksheet.Cells[1, 1] = reportName;             ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;             ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;             //写入字段             for (int i = 0; i < dt.Columns.Count; i++)             {                 worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;                 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];                 range.Interior.ColorIndex = 15;                 range.Font.Bold = true;             }             //写入数值             for (int r = 0; r < dt.Rows.Count; r++)             {                 for (int i = 0; i < dt.Columns.Count; i++)                 {                     worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();                 }                 rowRead++;                 percent = ((float)(100 * rowRead)) / totalCount;             }             range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);             range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);             if (dt.Rows.Count > 0)             {                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;             }             if (dt.Columns.Count > 1)             {                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;                 range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;             }             //保存文件             if (saveFileName != "")             {                 try                 {                     workbook.Saved = true;                     workbook.SaveCopyAs(saveFileName);                     fileSaved = true;                 }                 catch (Exception ex)                 {                     fileSaved = false;                     _ReturnStatus = -1;                     _ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;                 }             }             else             {                 fileSaved = false;             }             //释放Excel对应的对象             if (range != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(range);                 range = null;             }             if (worksheet != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);                 worksheet = null;             }             if (workbook != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                 workbook = null;             }             if (workbooks != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);                 workbooks = null;             }             xlApp.Application.Workbooks.Close();             xlApp.Quit();             if (xlApp != null)             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);                 xlApp = null;             }             GC.Collect();             return fileSaved;         }     } }