常用的操作Excel的类

 

public class ExcelHelper
    {
        /// <summary>
        /// 自定义要导出的字段的集合
        /// </summary>
        public Dictionary<string, string> Fileds
        {
            get;
            set;
        }

        #region "    [Import Excel]"

        /// <summary>
        /// 获得Excel里的Sheet集合
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        private static ArrayList GetExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();
                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2]);
                }
            }
            catch (Exception ex) { }

            return al;
        }

        /// <summary>
        /// 读取指定Excel里指定Sheet里的数据
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        private static DataSet GetDataByExcelSheet(string filepath, string sheetname)
        {
            string strConn;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
                oada.Fill(ds);
            }
            catch (Exception ex) { }
            return ds;

        }

        /// <summary>
        /// 从Excel导入,支持 .xls和.xlsx这2种格式

        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ImportFromExcel(string fileName)
        {
            FileInfo file = new FileInfo(fileName);
            if (!file.Exists)
            {
                throw new Exception("文件不存在");
            }
            string extension = file.Extension;
            string strConn = "";
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }

            OleDbConnection oleConn = new OleDbConnection(strConn);
            oleConn.Open();

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等

            DataTable dtSheetName = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //包含excel中表名的字符串数组

            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }
            DataSet result = new DataSet();
            DataTable dt;
            try
            {
                foreach (var table in strTableNames)
                {
                    dt = new DataTable();
                    string strSql = string.Format("select * FROM [{0}]", table);

                    OleDbCommand oleCom = new OleDbCommand(strSql, oleConn);

                    using (OleDbDataReader rdr = oleCom.ExecuteReader())
                    {
                        dt.Load(rdr);
                    }
                    result.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                //记录错误日志
            }
            finally
            {
                oleConn.Close();
            }
            return result;
        }

        #endregion

        #region "    [Export Excel]"

        /// <summary>
        /// 使用Office组件直接操作Excel (Excel2003/2007通用)
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExportToExcel(string fileName, DataTable dt)
        {
            int rowIndex = 1;     //Excel的行
            int colIndex = 0;      //Excel的列
            bool isSuccess = false;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            object ms = Type.Missing;
            Microsoft.Office.Interop.Excel.Workbook wk = excel.Workbooks.Add(ms);
            Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

            //添加Excel的表头

            foreach (DataColumn column in dt.Columns)
            {
                colIndex++;
                ws.Cells[1, colIndex] = column.ColumnName;
            }
            //填充Excel的数据

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;   //从第2行开始写入数据

                colIndex = 0;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    colIndex++;
                    ws.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
                }
            }
            try
            {
                wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
                isSuccess = true;
            }
            catch (Exception)
            {
                isSuccess = false;
            }
            excel.Quit();
            return isSuccess;
        }

        /// <summary>
        /// 使用Office组件直接操作Excel,导出自定义的字段 (Excel2003/2007通用)
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="fileds"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExportToExcel(string fileName, Dictionary<string, string> fileds, DataTable dt)
        {
            int rowIndex = 1;     //Excel的行
            int colIndex = 0;      //Excel的列
            bool isSuccess = false;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            object ms = Type.Missing;
            Microsoft.Office.Interop.Excel.Workbook wk = excel.Workbooks.Add(ms);
            Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

            //添加Excel的表头

            foreach (var item in fileds.Keys)
            {
                colIndex++;
                ws.Cells[1, colIndex] = item;
            }

            //-------------------------------------------------------------------------------------------
            Excel.Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 2]);
            range.NumberFormatLocal = "@";    //设置列为 文本格式
            range.Columns.AutoFit();        // 设置列宽为自动适应
            //-------------------------------------------------------------------------------------------

            //填充Excel的数据

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;   //从第2行开始写入数据

                colIndex = 0;
                foreach (var filed in fileds.Values)
                {
                    colIndex++;
                    //-------------------------------------------------------------------------------------------
                    //加 ' 设置单元格类型为 文本格式,防止出现 因为类型问题出现取不到列的数据

                    //ws.Cells[rowIndex, colIndex] ="'"+ dt.Rows[i][filed].ToString();
                    //-------------------------------------------------------------------------------------------
                    ws.Cells[rowIndex, colIndex] = dt.Rows[i][filed].ToString();
                }
            }
            try
            {
                wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
                isSuccess = true;
            }
            catch (Exception)
            {
                isSuccess = false;
            }
            excel.Quit();
            return isSuccess;
        }

        /// <summary>
        /// 使用文件流直接输出Excel文件 (仅适用于Excel2003,Excel 2007会报格式化错误警告)
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="dt"></param>
        public static void ExportToExcel(DataTable dt, string strFileName)
        {
            //清除Response缓存内容
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;

            //确定字符的编码格式

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            //HttpContext.Current.Response.Charset = "gb2312";
            //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            GridView dv = new GridView();
            dv.DataSource = dt;
            dv.DataBind();

            try
            {
                dv.EnableViewState = false;
            }
            catch (Exception ex)
            { }
            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
            dv.RenderControl(htmlTextWriter);

            //消除乱码特别设定，非常规方法
            string strExcel = "";
            strExcel += htmlTextWriter.InnerWriter.ToString();
            HttpContext.Current.Response.Write(strExcel);
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 导出为CSV文件 (Excel2003/2007通用)
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="dt"></param>
        public static void ExportToCsv(string FileName, DataTable dt)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", FileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            try
            {

                StringBuilder sb = new StringBuilder();
                //添加表头
                foreach (DataColumn column in dt.Columns)
                {
                    sb.AppendFormat("{0},", column.ColumnName.Replace(",", ""));
                }
                sb.Remove(sb.Length - 1, 1); //去掉最后一个","
                sb.Append("\n");

                //填充数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        sb.AppendFormat("{0},", dt.Rows[i][j].ToString().Replace(",", ""));
                    }
                    sb.Remove(sb.Length - 1, 1); //去掉最后一个","
                    sb.Append("\n");
                }
                HttpContext.Current.Response.Write(sb.ToString());
                HttpContext.Current.Response.Flush();
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);
            }
            finally
            {

            }
            HttpContext.Current.Response.End();
        }

        #endregion
    }