签到抽奖系统补充

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

namespace CommonHelper
{
    /// <summary>
    /// 标题：Excel文件助手类
    /// 描述：1.读取指定条件的Excel信息到内存中
    ///       2.将内存中的信息导出到Excel文件中
    ///       3.消除Excel文件中的空白记录
    /// </summary>
    public static class ExcelHelper
    {
        #region Field

        /// <summary>
        /// 读取Excel的连接字符串
        /// </summary>
        /// <example>
        /// Provider代表连接驱动4.0版本
        /// Data Source代表Excel的路径
        /// Extended Properties代表连接Excel的版本，对于Excel 97以上版本都用Excel 8.0
        /// HDR代表默认Excel第一行是否列名，Yse代表是可以直接读取，No反之
        /// IMEX代表
        /// </example>
        private const string strFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

        #endregion

        #region Method
        /// <summary>
        /// 把指定的DataTable里的空白行去除，返回一个没有空白行的DataTable
        /// </summary>
        /// <param name="dataTable">原始的DataTable</param>
        /// <returns>没有空白行的DataTable</returns>
        public static System.Data.DataTable DataTableEmptyRowsFilter(System.Data.DataTable dataTable)
        {
            System.Data.DataTable newDataTable = null;
            if (dataTable.Rows.Count != 0)
            {
                newDataTable = dataTable.Clone();
                foreach (DataRow dr in dataTable.Rows)
                {
                    if (dr[0].ToString() != string.Empty)
                    {
                        newDataTable.ImportRow(dr);
                    }
                }
            }
            return newDataTable;
        }

        /// <summary>
        /// 读取Excel文件指定sheet内容到DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetName">Excel指定工作表名,""默认工作表1</param>
        /// <returns>DataTable</returns>
        public static System.Data.DataTable ExcelToDataTable(string filePath, string sheetName)
        {
            DataSet ds = toDataSet(filePath);

            System.Data.DataTable dt = new System.Data.DataTable();
            if (sheetName == "")
            {
                dt = ds.Tables[0];
            }
            else
            {
                dt = ds.Tables[sheetName];
            }
            return dt;
        }

        /// <summary>
        /// 读取Excel文件内容到DataSet
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns>DataSet</returns>
        public static DataSet toDataSet(string filePath)
        {
            string connStr = string.Empty;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            string sql_F = "Select * FROM [{0}]";

            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            System.Data.DataTable dtSheetName = null;

            DataSet ds = new DataSet();
            try
            {
                // 初始化连接，并打开
                conn = new OleDbConnection(connStr);
                conn.Open();

                // 获取数据源的表定义元数据                        

                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                //  定义数据源的工作表名数据列表数组   
                //string SheetName = "";
                string[] SheetName = new string[dtSheetName.Rows.Count];

                // 初始化适配器
                da = new OleDbDataAdapter();
                for (int i = 0; i < SheetName.Length; i++)
                {
                    SheetName[i] = (string)dtSheetName.Rows[i]["TABLE_NAME"];

                    if (SheetName.Contains("$") && !SheetName[i].Replace("'", "").EndsWith("$"))
                    {
                        continue;
                    }

                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
                    DataSet dsItem = new DataSet();
                    //da.Fill(dsItem, tblName);
                    da.Fill(dsItem, SheetName[i]);

                    ds.Tables.Add(dsItem.Tables[0].Copy());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // 关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return ds;
        }

        /// <summary>
        /// 读取Excel文件内指定sheet页的数据到DataSet
        /// </summary>
        /// <param name="strFilePath">Excel文件完整路径</param>
        /// <param name="dataTable">读取的表格数据</param>
        /// <param name="strSheetName">sheet名称</param>
        /// <param name="strNewSheetName">dataTable名称</param>
        /// <returns>操作错误信息，若为空则执行成功</returns>
        public static string TryRead(string strFilePath, out System.Data.DataTable dataTable, string strSheetName = "Sheet", string strNewSheetName = "ExcelInfo")
        {
            string errormsg = string.Empty;

            if (!string.IsNullOrEmpty(strFilePath) && File.Exists(strFilePath))
            {
                string strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + strFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                //string strConn = string.Format(strFormat, strFilePath);
                string strSQL = "SELECT * FROM [" + strSheetName + "$]";
                //string strSQL = string.Format("SELECT * FROM [{0}$]", strSheetName);
                try
                {
                    using (OleDbDataAdapter ExcelDA = new OleDbDataAdapter(strSQL, strConn))
                    {
                        DataSet ExcelDS = new DataSet();
                        ExcelDA.Fill(ExcelDS, strNewSheetName);
                        dataTable = DataTableEmptyRowsFilter(ExcelDS.Tables[0]);
                    }
                }
                catch (Exception err)
                {
                    errormsg = err.Message;
                    dataTable = null;
                }
            }
            else
            {
                errormsg = "The file path of the file does not exist";
                dataTable = null;
            }

            return errormsg;
        }


        /// <summary>
        /// 将DataTable写入指定路径Excel文件
        /// </summary>
        /// <remarks>
        /// 采用将导出的数据保存到数组，然后一次过导出，以提高速度
        /// excelSheet.Merge(Missing.Value); 合并 
        /// excelSheet.Font.Bold=true; 设置粗体
        /// excelSheet.Font.Size=12;设置字体大小
        /// excelSheet.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;水平对齐设置
        /// excelSheet.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;垂直对齐设置 
        /// excelSheet.FormulaR1C1=公式; 公式设置
        /// excelSheet.ColumnWidth=20;  设置列宽
        /// excelSheet.RowHeight=20; 设置行高
        /// </remarks>
        /// <param name="dTable">数据表</param>
        /// <param name="strFilePath">导出路径文件夹路径</param>
        /// <param name="SaveMode">false新建Excel保存，true向已存在的Excel中添加Sheet保存</param>
        /// <param name="strSheetName">导出Excel的Sheet名</param>
        /// <param name="startCol">Excel内容开始列，默认为1，第一列为标题</param>
        /// <param name="startRow">Excel内容开始行，默认为1，第一行为标题</param>
        public static string DataTableToExcel(System.Data.DataTable dTable, string strFiledirectPath, bool SaveMode, string strSheetName = "", int startRow = 1, int startCol = 1)
        {
            string errorMsg = string.Empty;

            if (dTable == null || dTable.Rows.Count <= 0 || startRow <= 0 || startCol <= 0)
            {
                return errorMsg = "DataTable is null";
            }

            var excel = new Application();
            Workbook excelBook;
            if (SaveMode)
                excelBook = excel.Workbooks.Open(strFiledirectPath);
            else
                excelBook = excel.Workbooks.Add(Type.Missing);
            var excelSheet = (Worksheet)excelBook.Sheets.Add();
            var rowCount = dTable.Rows.Count;
            var colCount = dTable.Columns.Count;

            //设置新建Sheet的名字
            if (string.IsNullOrWhiteSpace(strSheetName))
                excelSheet.Name = "Sheet" + excelBook.Sheets.Count;
            else
                excelSheet.Name = strSheetName;

            //二维数组定义是多一个标题行
            var dataArray = new object[rowCount + 1, colCount];

            for (var j = 0; j < colCount; j++)
            {
                //导出字段标题
                dataArray[0, j] = dTable.Columns[j].Caption;

                //根据各列的数据类型设置Excel的格式。
                switch (dTable.Columns[j].DataType.ToString())
                {
                    case "System.String":
                        excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
                        NumberFormatLocal = "@";
                        break;
                    case "System.DateTime":
                        excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
                            NumberFormatLocal = "yyyy-MM-dd HH:mm:ss";
                        break;
                    //可以根据自己的需要扩展。
                    default:
                        excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
                            NumberFormatLocal = "G/通用格式";
                        break;
                }
                for (int i = 0; i < rowCount; i++)
                {
                    dataArray[i + 1, j] = dTable.Rows[i][j];
                }
            }

            //写入Excel Sheet
            excelSheet.get_Range(excel.Cells[startRow, startCol] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Value2 = dataArray;
            //设置列头为粗体字
            excelSheet.get_Range(excel.Cells[1, 1] as Range, excel.Cells[1, colCount] as Range).Font.Bold = true;
            //设置列头底色为灰色
            excelSheet.get_Range(excel.Cells[1, 1] as Range, excel.Cells[1, colCount] as Range).Interior.ColorIndex = 15;
            //设置内容的字体大小为9
            excelSheet.get_Range(excel.Cells[2, 1] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Font.Size = 9;
            //设置内容的字体微软雅黑
            excelSheet.get_Range(excel.Cells[2, 1] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Font.Name = "微软雅黑";
            //设置Sheet的名称
            //if (!String.IsNullOrWhiteSpace(dTable.TableName))
            //    excelSheet.Name = dTable.TableName;

            //保存文档
            try
            {
                //strFiledirectPath = Path.Combine(strFiledirectPath, dTable.TableName + ".xls");
                excelBook.Saved = true;
                if (SaveMode)
                    excelBook.Save();
                else
                    excelBook.SaveCopyAs(strFiledirectPath);
            }
            catch (Exception ex)
            {
                errorMsg = ex.Message;
            }
            finally
            {
                excel.Quit();
                GC.Collect();
            }

            return errorMsg;
        }
        #endregion
    }
}

 

转载于:https://my.oschina.net/u/3736209/blog/1580918