Window From Excel的导入和导出

Excel 对数据的操作主要还是借助于微软的Excell.dll文件，对Excell的使用可以通过Window API也可以通过.FrameWork来使用这个Excell.dll ，本文仅仅是自己对知识的回顾，通过一个简单的自定义类，进行阐述Excell的使用。具体代码如下：

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Diagnostics;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Drawing;
using System.Collections;

namespace LOGHELPTOOL
{
    class ExcelIO
    {
        private Excel.Application xlApp;
        private Excel.Workbooks workbooks = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        private Excel.Range range = null;
        private string returnMessage = null;
        //查询Excel是否存在
        protected bool IsExistExcel()
        {
            try
            {
                xlApp = new Excel.Application();
                if (xlApp == null)
                {
                    returnMessage = "无法创建Excel对象,可能是您的计算机没有安装Excel";
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnMessage = "请正确安装Excel！";
                return false;
            }
            return true;
        }

        //设置保存路径
        public static string SaveFileDialog()
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.DefaultExt = "xls";
            sfd.Filter = "Excel文件(*.xls)|*.xls";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                return sfd.FileName;
            }
            return string.Empty;
        }

        public static string OpenFileDialog()
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.DefaultExt = "xls";
            sfd.Filter = "Excel文件(*.xls)|*.xls";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                return sfd.FileName;
            }
            return string.Empty;
        }
        //设置样式
        protected void SetCellsBorderAround()
        {
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, null);
        }

        //导入DataTable进入Excel
        public bool DataTableExcel(DataTable dt, string saveFileName, string ReportName)
        {
            if (!IsExistExcel())
            {
                returnMessage = "没有安装Excel";
                return false;
            }
            bool fileSaved = false;
            if (dt == null)
            {
                returnMessage = "无引出数据";
                return false;
            }
            if (!saveFileName.Contains(":"))
            {
                returnMessage = "引出路径有误!请选择正确路径";
                return false;
            }
            workbooks = xlApp.Workbooks;
            workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得第一sheet1
            worksheet.Cells.Font.Size = 10;
            worksheet.Cells.NumberFormat = "@";
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            int rowIndex = 0;
            //第一行为报表名称,如果为null,则不保存该行
            ++rowIndex;
            worksheet.Cells[rowIndex, 1] = ReportName;
            range = (Excel.Range)worksheet.Cells[rowIndex, 1];
            range.Font.Bold = true;

            //写入字段(标题)
            ++rowIndex;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[rowIndex, i + 1] = dt.Columns[i].ColumnName;
                range = (Excel.Range)worksheet.Cells[rowIndex, i + 1];
                range.Font.Color = ColorTranslator.ToOle(Color.Blue);
                range.Interior.Color = dt.Columns[i].Caption == "表体" ? ColorTranslator.ToOle(Color.SkyBlue) : ColorTranslator.ToOle(Color.Yellow);
            }
            //写入数据
            ++rowIndex;
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + rowIndex, 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]);
            this.SetCellsBorderAround();

            //列宽自适应
            range.EntireColumn.AutoFit();

            //保存文件
            if (saveFileName != "")
            {
                try {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch(Exception ex)
                {
                    fileSaved = false;
                    returnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
                }
            }
            else
            {
                fileSaved = false;
            }

            Dispose();
            return fileSaved;
         }

        //导入Excel到DataSet
        public DataSet ImportExcel(string fileName)
        {
            try {
                if (!IsExistExcel())
                {
                    returnMessage = "没有安装Excel";
                    return null;
                }
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            }
            catch(Exception ex)
            {
                returnMessage = "Excel文件处于打开状态,请保存关闭";
                return null;
            }
            int n = workbook.Worksheets.Count;
            string[] sheetSet = new string[n];
            for (int i = 0; i < n; i++)
            {
                sheetSet[i] = ((Excel.Worksheet)workbook.Worksheets[i + 1]).Name;

            }
            Dispose();
            DataSet ds = null;
            List<string> connStrs = new List<string>();
            connStrs.Add("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"");
            connStrs.Add("Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"");
            foreach (string connStr in connStrs)
            {
                ds = GetDataSet(connStr, sheetSet);
                if (ds != null)
                    break;
            }
            return ds;
        }

        protected DataSet GetDataSet(string connStr, string[] sheetSet)
        {
            DataSet ds = null;
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try {
                    conn.Open();
                    OleDbDataAdapter da;
                    ds = new DataSet();
                    for (int i = 0; i < sheetSet.Length; i++)
                    {
                        string sql = "select * from [" + sheetSet[i] + "$]";
                        da = new OleDbDataAdapter(sql, conn);
                        da.Fill(ds, sheetSet[i]);
                        da.Dispose();
                    }
                    conn.Close();
                    conn.Dispose();
                }
                catch(Exception ex)
                {
                    return null;
                }
            }
            return ds;
        }

        bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            try {
                if (disposed)
                {
                    if (disposing)
                    {
                        if (range != null)
                        {
                            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(range);
                            range = null;
                        }
                        if (workbook != null)
                        {
                            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                            workbook = null;
                        }
                        if (workbooks!=null)
                        {
                            xlApp.Application.Workbooks.Close();
                            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbooks);
                            workbooks = null;
                        }
                        if (xlApp != null)
                        {
                            xlApp.Quit();
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                        }

                        int generation = GC.GetGeneration(xlApp);//返回当前代数
                        System.GC.Collect(generation);//强制从0-generation代数的垃圾回收
                        
                    }
                    //非托管资源的释放
                    //KillExcel();
                }
                disposed = true;
            }
            catch {
                throw;
            }
        }

        public void Dispose()
        {
            try { 
                Dispose(true);
                GC.SuppressFinalize(this);//取消垃圾回收

            }
            catch {
                throw;
            }
        }
        //关闭
        public void Close()
        {
            try { this.Dispose(); }
            catch { throw; }
        }


        ~ExcelIO()
        {
            try {
                Dispose(false);
            }
            catch {
                throw;
            }
        }

        private void KillExcel()
        {
            try {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {
                        p.Kill();
                    }
                }
            }
            catch {
                throw;
            }
        }
    }
}

 

下面是简单一些Excel的样式设计：

xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种

//设置整个报表的标题格式
  xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
  xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
  //设置整个报表的标题为跨列居中
  xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
  xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

转载地址：http://www.2cto.com/kf/201205/131076.html   http://wenda.tianya.cn/wenda/thread?tid=2764e58c48033157&clk=cts_st

xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗

rngE.NumberFormatLocal = @"yyyy-mm-dd";//日期型格式 对于格式，很是诡异

友情网址：http://blog.sina.com.cn/s/blog_64e3235c01013koy.html