C#实现Excel的导入与导出《转》

转:http://blog.csdn.net/xuenzhen123/article/details/4713019ss

前两天研究如何使用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()

        {

        }

        /// <summary>

        /// Excel文件的导出

        /// 其中每个工作表为一个DataTable

        /// </summary>

        /// <param name="fileName">Excel文件的完整路径</param>

        /// <returns></returns>

        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;

        }

 

        /// <summary>

        /// Excel导入

        /// 每个Datatable为一个工作表,工作表的名字为Datatable的名字

        /// </summary>

        /// <param name="ds">数据源</param>

        /// <param name="saveFileName">要保存的Excel文件的完整路径</param>

        /// <returns></returns>

        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;

 

各位网友对以上算法有什么建议请指出,谢谢。

转载于:https://www.cnblogs.com/love-liu/archive/2012/03/01/2375379.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值