IMEX.mes.wcf/DK.Basics/Library/ExcelLayer.cs

/*******************************************************************************
 * Copyright(c) 2014 dongke All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：ExcelLayer.cs
 *      2.功能描述：类文件
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      陈冰            2014/9/22           1.00            新建
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using System.IO;
using System.Reflection;

namespace Dongke.IBOSS.PRD.Basics.Library
{
    public class ExcelLayer
    {
        /// <summary>
        /// Retireves the data from Excel Sheet to a DataTable.
        /// </summary>
        /// <param name="FileName">File Name along with path from the root folder.</param>
        /// <param name="TableName">Name of the Table of the Excel Sheet. Sheet1$ if no table.</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string FileName, string TableName, int v)
        {
            OleDbConnection objConn = null;
            try
            {
                string strPath = FileName;
                DataSet ds = new DataSet();
                string sConnectionString = null;
                if (v == 0)
                {
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + strPath + "; " + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1;';";
                }
                else if (v == 1)
                {
                    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + strPath + "; " + "Extended Properties='Excel 12.0; HDR=Yes; IMEX=1;';";
                }

                objConn = new OleDbConnection(sConnectionString);
                objConn.Open();

                TableName += "$";
                string strTableName = TableName;
                try
                {
                    DataTable sheetNames = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    int index = 0;
                    foreach (DataRow drTableName in sheetNames.Rows)
                    {
                        string tName = drTableName["TABLE_NAME"].ToString();
                        if (index == 0)
                        {
                            strTableName = tName;
                        }
                        index++;

                        if (tName == TableName)
                        {
                            strTableName = TableName;
                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.Trace(LogPriority.Error, "ExcelLayer",
                        "GetDataTable(FileName, TableName) GetSheetNames", ex.ToString());
                }

                using (OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + strTableName + "]", objConn))
                {
                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                    objAdapter1.SelectCommand = objCmdSelect;
                    objAdapter1.Fill(ds);
                    objConn.Close();
                    objConn = null;
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn = null;
                }
                //throw ex;
                //Log your exception here.//
                OutputLog.Trace(LogPriority.Error, "ExcelLayer",
                    "GetDataTable(FileName, TableName)", ex.ToString());
                return (DataTable)null;
            }
        }

        /// <summary>
        /// Retireves the data from Excel Sheet to a DataTable.
        /// </summary>
        /// <param name="FileName">File Name along with path from the root folder.</param>
        /// <param name="TableName">Name of the Table of the Excel Sheet. Sheet1$ if no table.</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string FileName, int v)
        {
            OleDbConnection objConn = null;
            try
            {
                string strPath = FileName;
                DataSet ds = new DataSet();
                string sConnectionString = null;
				// Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;
                if (v == 0)
                {
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + strPath + "; " + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1;';";
                }
                else if (v == 1)
                {
                    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + strPath + "; " + "Extended Properties='Excel 12.0; HDR=Yes; IMEX=1;';";
                }

                objConn = new OleDbConnection(sConnectionString);
                objConn.Open();

                string strTableName = string.Empty;
                DataTable sheetNames = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow drTableName in sheetNames.Rows)
                {
                    strTableName = drTableName["TABLE_NAME"].ToString();
                    if (!string.IsNullOrEmpty(strTableName))
                    {
                        break;
                    }
                }
                using (OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + strTableName + "]", objConn))
                {
                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                    objAdapter1.SelectCommand = objCmdSelect;
                    objAdapter1.Fill(ds);
                    objConn.Close();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn = null;
                }
                //throw ex;
                //Log your exception here.//
                OutputLog.Trace(LogPriority.Error, "ExcelLayer",
                    "GetDataTable(FileName)", ex.ToString());
                return (DataTable)null;
            }
        }


        public static void ExportExcel(DataTable exportDataTable, string text, string tableName, string fileNameString)
        {
            Interop.Excel.Application oXL = null;
            Interop.Excel._Workbook oWB = null;
            Interop.Excel._Worksheet oSheet = null;
            Interop.Excel.Range oRng = null;
            Interop.Excel.Range range = null;

            try
            {
                if (exportDataTable.Rows.Count == 0)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "没有导出的数据"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }

                SaveFileDialog dialog = new SaveFileDialog();


                if (string.IsNullOrEmpty(fileNameString.Trim()))
                {
                    return;
                }
                int rowCount = exportDataTable.Rows.Count;
                int columnCount = exportDataTable.Columns.Count;

                // 行数不能大于65536
                if (rowCount > 65536)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "导出数据太多(最多不能超过65536条)"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }

                // 列数不能大于255
                if (columnCount > 255)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "导出数据列数太多(最多不能超过255列)"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }

                // 验证以fileNameString命名的文件是否存在，如果存在提示
                FileInfo file = new FileInfo(fileNameString);

                if (file.Exists)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "文件名" + fileNameString + "已存在"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }

                oXL = new Interop.Excel.Application();

                if (oXL == null)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "请确保您的电脑已经安装Excel"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }
                oXL.UserControl = false;
                oXL.Visible = false;
                //创建一个新的workboot
                oWB = (Interop.Excel._Workbook)(oXL.Workbooks.Add(true));
                oSheet = (Interop.Excel._Worksheet)oWB.ActiveSheet;
                if (oSheet == null)
                {
                    MessageBox.Show(string.Format(Messages.MSG_CMN_W007,
                                            "请确保您的电脑已经安装Excel"),
                                            text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Warning);
                    return;
                }
                oSheet.Name = tableName;

                List<string> nodeList = new List<string>();

                int maxColumnsCount = 0;
                int j = 1;
                for (int i = 0; i < exportDataTable.Columns.Count; i++)
                {
                    //TreeNode node = (TreeNode)exportDataTable.ColumnList[i];



                    if (exportDataTable.Columns[i].DataType == typeof(string))
                    {
                        //设置excel文件中为文本型
                        range = oSheet.get_Range(oSheet.Cells[1, maxColumnsCount + 1],
                            oSheet.Cells[exportDataTable.Rows.Count + 3, maxColumnsCount + 1]);
                        range.NumberFormat = "@";//格式设置为文本
                    }

                    //string[] fullpath = node.FullPath.Split('\\');

                    // 获取对于excel中列的列号
                    string excelcolumn = GetCharByInt(maxColumnsCount + 1);

                    oSheet.Cells[j, maxColumnsCount + 1] = exportDataTable.Columns[i].ColumnName;
                    //oSheet.get_Range(excelcolumn + j, excelcolumn + (columnDeep - j + 1)).MergeCells = true;
                    //oSheet.get_Range(excelcolumn + j, excelcolumn + (columnDeep - j + 1)).HorizontalAlignment
                    //                        = Interop.Excel.Constants.xlCenter;//居中


                    maxColumnsCount++;

                }

                // 控制表头的格式信息
                for (int i = 1; i < 4; i++)
                {
                    oRng = oSheet.get_Range("A" + i, GetCharByInt(maxColumnsCount) + i);
                    oRng.EntireColumn.AutoFit();
                    oSheet.get_Range("A" + i, GetCharByInt(maxColumnsCount) + i).Borders.Weight = Interop.Excel.XlBorderWeight.xlThin;
                }

                j = 4;// 从第四行填数据
                string maxColumnStr = GetCharByInt(maxColumnsCount) + j;

                for (int x = 0; x < exportDataTable.Rows.Count; x++)
                {
                    //范围内单元格自动适应文字的长短
                    oRng = oSheet.get_Range("A" + j, maxColumnStr);
                    oRng.EntireColumn.AutoFit();
                    oSheet.get_Range("A" + j, maxColumnStr).Borders.Weight = Interop.Excel.XlBorderWeight.xlThin;

                    int b = 0;

                    for (int i = 0; i < exportDataTable.Columns.Count; i++)
                    {

                        oSheet.Cells[j, b + 1] = exportDataTable.Rows[x][i];
                        b++;

                    }

                    j++;
                }
                object missing = System.Reflection.Missing.Value;
                oSheet.Protect(missing, missing, false, missing, true, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, false); //生成模板
                //oSheet.PageSetup.Orientation = Interop.Excel.XlPageOrientation.xlPortrait; //设置为纵向打印
                oSheet.PageSetup.Orientation = Interop.Excel.XlPageOrientation.xlLandscape; //设置为横向打印
                oSheet.PageSetup.PaperSize = Interop.Excel.XlPaperSize.xlPaperA4;       //设置打印字为A4纸张
                // 其中 56 为 2003兼容格式
                oXL.ActiveWorkbook.SaveAs(fileNameString, 56, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            }
            catch (Exception)
            {
                MessageBox.Show("导出失败",
                                    text,
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Error);
                return;
            }
            finally
            {
                // 关闭Excel应用
                if (oXL != null)
                {
                    oXL.ActiveWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                    if (oXL.Workbooks != null)
                    {
                        oXL.Workbooks.Close();
                    }
                    oXL.Quit();//Excel正常退出
                    System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oXL);
                    System.GC.Collect();
                    MessageBox.Show("导出成功", text,
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                }
            }
        }

        /// <summary>
        /// 由Int型获取相应的Char字符串（只支持2位，导出EXCEL时使用）
        /// </summary>
        /// <returns></returns>
        private static string GetCharByInt(int charint)
        {
            try
            {
                int a = charint / 26;
                int b = charint % 26;

                if (charint % 26 == 0)
                {
                    a = a - 1;
                    b = 26;
                }

                string aa = "";
                string bb = "";

                if (a != 0)
                {
                    aa = (char)(a + 64) + "";
                }

                if (b != 0)
                {
                    bb = (char)(b + 64) + "";
                }

                return aa + "" + bb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




    }
}