C#实现Excel的导入与导出

前两天研究如何使用C#实现Excel数据的导入与导出，在网上查找大量资料，最后经过整理，写了一个通用的类，与大家分享一下，共同学习。代码如下：

 public class ExcelIO
    {
        private int _ReturnStatus;
        private string _ReturnMessage; 

        private const string CREATE_ERROR = "Can not create excel file, may be your computer has not installed excel!";
        private const string IMPORT_ERROR = "Your excel file is open, please save and close it.";
        private const string EXPORT_ERROR = "This is an error that the excel file may be open when it be exported. /n"; 

        public int ReturnStatus
        {
            get { return _ReturnStatus; }
        } 

        public string ReturnMessage
        {
            get { return _ReturnMessage; }
        }

        public ExcelIO()
        {
        }

        /// 
        /// Excel文件的导出
        /// 其中每个工作表为一个DataTable
        /// 
        /// Excel文件的完整路径
        /// 
        public DataSet ImportExcel(string fileName)
        {
            Excel.Application xlApp = new Excel.ApplicationClass();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = CREATE_ERROR;
                return null;
            } 

            Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _ReturnMessage = IMPORT_ERROR;
                return null;
            } 

            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] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
            } 

            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(); 

            DataSet ds = new DataSet();
            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;
                for (int i = 1; i <= n; i++)
                {
                    string sql = "select * from [" + SheetSet[i - 1] + "$] ";
                    da = new OleDbDataAdapter(sql, conn);
                    da.Fill(ds, SheetSet[i - 1]);
                    da.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return ds;
        }
 

        /// 
        /// Excel导入
        /// 每个Datatable为一个工作表，工作表的名字为Datatable的名字
        /// 
        /// 数据源
        /// 要保存的Excel文件的完整路径
        /// 
        public bool ExportExcel(DataSet ds, string saveFileName)
        {
            if (ds == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "The DataSet is null!";
                return false;
            } 

            bool fileSaved = false;
            Excel.Application xlApp = new Excel.ApplicationClass();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = CREATE_ERROR;
                return false;
            } 

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            for (int j = ds.Tables.Count - 1; j >= 0; j--)
            {
                DataTable dt = ds.Tables[j];
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value,Missing.Value, Missing.Value, Missing.Value);
                worksheet.Name = ds.Tables[j].TableName.ToString();
                worksheet.Cells.Font.Size = 10;
                Excel.Range range;

                long totalCount = dt.Rows.Count;
                long rowRead = 0;
                float percent = 0;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    range = (Excel.Range)worksheet.Cells[1, 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 + 2, 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 + 1, dt.Columns.Count]);
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                if (dt.Rows.Count > 0)
                {
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
                }
                if (dt.Columns.Count > 1)
                {
                    range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                    range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
                    range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
                }
                if (range != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    range = null;
                }
                if (worksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                }
            }

            ((Excel.Worksheet)workbook.Worksheets[ds.Tables.Count + 1]).Delete();

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel8, miss, miss, miss, miss,
                        Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    _ReturnStatus = -1;
                    _ReturnMessage = EXPORT_ERROR + ex.Message;
                }
            }
            else
            {
                fileSaved = false;
            } 

            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;
        }
    }

当然，你需要添加引用：

using Excel = Microsoft.Office.Interop.Excel;

using System.Data.OleDb;