Excel导入导出数据库02

excel导入时还要保存字体、其背景颜色等信息时读取方法就要改变：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Runtime.InteropServices;

namespace WinOrderAd
{
    public class Excel
    {
        public string FilePath
        {
            get;
            set;
        }
        public Dictionary<string, string> FiledNames
        {
            get;
            set;
        }
        public Excel()
        {
        }

        public DataSet ImportExcel()//若只需要知道数据就用此方法
        {

            try
            {
                string strConn;
                if (Path.GetExtension(FilePath) == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                System.Data.DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                DataSet OleDsExcle = new DataSet();
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    string tableName = table.Rows[i]["Table_Name"].ToString();
                    tableName = tableName.Replace("'", "");
                    if (tableName.EndsWith("$"))
                    {
                        string sql = "SELECT * FROM [" + tableName + "]";
                        OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                        OleDaExcel.Fill(OleDsExcle, tableName);
                        OleConn.Close();
                    }
                }
                return OleDsExcle;
            }
            catch (Exception err)
            {
                throw err;
            }
        }


        /// <summary>
        /// 用Excel Com组件方式读取Excel内容到DataSet(兼容性较高)
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataSet ToDataTableEx()
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            excel.Visible = false;
            excel.ScreenUpdating = false;
            excel.DisplayAlerts = false;

            excel.Workbooks.Add(FilePath);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            Range rangecell = null;
            DataSet ds = new DataSet();
            try
            {
                //遍历Worksheets中的每张表
                for (int i = 1; i <= excel.Worksheets.Count; i++)
                {
                    //获得指定表
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[i];

                    System.Data.DataTable dt = new System.Data.DataTable();

                    //取表明赋值到dt TableName
                    dt.TableName = worksheet.Name;

                    worksheet.Columns.EntireColumn.AutoFit();

                    int row = worksheet.UsedRange.Rows.Count;
                    int col = worksheet.UsedRange.Columns.Count;

                    for (int c = 1; c <= col; c++)
                    {
                        dt.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text));
                    }
                    //添加一样式列
                    dt.Columns.Add(new DataColumn("Style"));

                    for (int r = 2; r <= row; r++)
                    {
                        DataRow newRow = dt.NewRow();
                        for (int c = 1; c <= col; c++)
                        {
                            rangecell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c];
                            newRow[c - 1] = rangecell.Text;
                            if (c == 3)
                            {
                                //取信息的字体颜色与背景颜色
                                newRow[col] = rangecell.Font.Color + "|" + rangecell.Interior.Color;
                            }
                        }
                        dt.Rows.Add(newRow);
                    }
                    ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (worksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rangecell);
                    rangecell = null;
                }
                excel.Workbooks.Close();
                excel.Quit();
                int generation = System.GC.GetGeneration(excel);
                if (excel != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    excel = null;
                }
                System.GC.Collect(generation);
            }
            return ds;
        }


    }
}

导出设置其样式

 public void ExportExcel(string[] listTableName, string[] listColName, List<List<Ad>> resource, string exporFilePath)
        {
            List<Ad> list = null;
            List<Ad> listresource = null;
            Microsoft.Office.Interop.Excel.Application app =
               new Microsoft.Office.Interop.Excel.ApplicationClass();

            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;
            Workbook wBook = app.Workbooks.Add(true);

            InsertLinkWorksheet(app, wBook);

            Worksheet wSheet = null;
            Range rangeResource = null;
            Range rangeContent = null;
            Range rangeTitle = null;
            //for (int k = 0; k < listList.Count; k++)
            for (int k = listList.Count - 1; k >= 0; k--)
            {
                listresource = resource[k];//已排好的数据
                wSheet = wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
                wSheet.Name = listTableName[k];

                try
                {
                    int resourcenum = listresource.Count;
                    if (resourcenum > 0)
                    {
                        for (int i = 0; i < resourcenum; i++)
                        {
                            wSheet.Cells[i + 2, 1] = (i + 1);
                            wSheet.Cells[i + 2, 2] = listresource[i].ID;
                            wSheet.Cells[i + 2, 3] = listresource[i].Info;
                            wSheet.Cells[i + 2, 4] = listresource[i].Format;
                            wSheet.Cells[i + 2, 5] = listresource[i].Times;
                            wSheet.Cells[i + 2, 6] = listresource[i].Attach;
                            wSheet.Cells[i + 2, 7] = listresource[i].Frequency;
                            wSheet.Cells[i + 2, 8] = listresource[i].Same;
                            wSheet.Cells[i + 2, 9] = listresource[i].Dif;
                            wSheet.Cells[i + 2, 10] = listresource[i].Balanced;

                            if (listresource[i].RowStyle.Split('|')[0] != "")
                            {
                                rangeResource = wSheet.get_Range(wSheet.Cells[i + 2, 1], wSheet.Cells[i + 2, 10]);
                                rangeResource.Font.Color = listresource[i].RowStyle.Split('|')[0];
                                rangeResource.Interior.Color = listresource[i].RowStyle.Split('|')[1];
                            }
                        }
                    }
 78                     int m = 0;
                    int col = listColName.Count();
                    for (m = 0; m < col; m++)
                    {
                        string headname = listColName[m];//单元格头部
                        wSheet.Cells[1, 1 + m] = headname;
                    }
                    //内容
                    rangeContent = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[list.Count + resourcenum + 2 + 1, col]);
                    rangeContent.Borders.Color = System.Drawing.Color.Black.ToArgb();
                    //rangeContent.Interior.Color = 10092543;    //设置区域背景色
                    rangeContent.VerticalAlignment = -4108;//竖向居中
                    rangeContent.HorizontalAlignment = -4108;//横向居中
                    rangeContent.RowHeight = 18;
                    rangeContent.EntireColumn.AutoFit();//自动调整列宽
                    //标题
                    rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, col]);
                    rangeTitle.Borders.Color = System.Drawing.Color.Black.ToArgb();
                    rangeTitle.Interior.Color = 65280;    //设置区域背景色
                    rangeTitle.VerticalAlignment = -4108;
                    rangeTitle.HorizontalAlignment = -4108;
                    rangeTitle.RowHeight = 18;
                    rangeTitle.EntireColumn.AutoFit();
                    //冻结首行
                    //rangeTitle.Select();
                    app.ActiveWindow.SplitColumn = 0;
                    app.ActiveWindow.SplitRow = 1;
                    app.ActiveWindow.FreezePanes = true;
                    //rangeTitle.Font.Bold = true;    //设置字体粗体。
                }
                catch (Exception err)
                {
                    throw err;
                }
                finally
                {

                }
            }
            //设置禁止弹出保存和覆盖的询问提示框   
            app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;
            ((Worksheet)wBook.Worksheets["Sheet1"]).Delete();
            try
            {
                wBook.Saved = true;
                //保存工作簿   
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                wBook.SaveAs(exporFilePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rangeResource != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeResource);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeContent);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeTitle);
                rangeResource = null;
                rangeContent = null;
                rangeTitle = null;
            }

            if (wSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
                wSheet = null;
            }

            if (wBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
                wBook = null;
            }
            app.Workbooks.Close();
            app.Quit();
            int generation = System.GC.GetGeneration(app);
            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
            GC.Collect(generation);
        }

listTableName为多个sheet的名称

listColName为每页的列名

resource为sheet数据队列

exporFilePath为要保存的路径