C#实现MES系统导出多个页签的Excel文件

C#实现MES系统导出多个页签的Excel文件 

SpreadsheetLight.dll

Excel.dll

   #region EXCEL
    public void DeleteFile(string FilePath)
    {
        if (!Directory.Exists(FilePath))
            Directory.CreateDirectory(FilePath);
        var dirinfo = new DirectoryInfo(FilePath);
        var fileinfo = dirinfo.GetFiles("*.xls*");

        foreach (FileInfo fi in fileinfo)
        {
            if (fi.LastWriteTime.AddDays(1) <= DateTime.Now)
            {
                fi.Delete();
            }
        }
    }
    protected void btToExcel_Click(object sender, EventArgs e)
    {
        var Path = @"D:\MESApps\EEP2015\Excel\";
        DeleteFile(Path);
        var EC = new ExcelConverter();
        var FileName = "RPT2278A32_" + DateTime.Now.ToString("MMddHHmmss") + ".xlsx";
        CreateExcel(Path, FileName);

        #region 重命名Sheet
        SLDocument SlDoc = new SLDocument(Path + FileName);
        SlDoc.RenameWorksheet("工作表1", "機台出現地點");
        SlDoc.RenameWorksheet("工作表2", "機台基本信息");
        SlDoc.RenameWorksheet("工作表3", "過站記錄");
        SlDoc.SaveAs(Path + FileName);
        #endregion
        while (wdsSNInfo.GetNextPacket()) { }
        addSheet(Path + FileName, wdsSNInfo.InnerDataSet, "機台出現地點");

        while (wdsWIP_Tracking.GetNextPacket()) { }
        addSheet(Path + FileName, wdsWIP_Tracking.InnerDataSet, "機台基本信息");

        while (wdsLog.GetNextPacket()) { }
        addSheet(Path + FileName, wdsWIP_Tracking.InnerDataSet, "過站記錄");
        EC.WebopenExcel(Response, Path + FileName);
    }
    /// <param name="filePath">filePath 为Excel保存的路径</param>
    public static void CreateExcel(string filePath, string fileName)
    {
        Excel.Application excel = new Excel.Application();
        //EXCEL读写是否可见
        excel.Visible = false;
        excel.Application.Workbooks.Add();
        Excel.Workbook myBook;
        myBook = excel.Workbooks[1];
        //保存路径
        string path = filePath + fileName;
        Excel.Worksheet mySheet;
        mySheet = (Excel.Worksheet)myBook.ActiveSheet;
      
        //页签名
        //mySheet.Name = "Student";

        //Cell项
        mySheet.Cells.Select();
        mySheet.Cells.NumberFormatLocal = "@";
        mySheet.Cells.EntireColumn.AutoFit();
        //保存Excel
        myBook.Close(true, path, null);
        excel.DisplayAlerts = true;
        excel.AlertBeforeOverwriting = true;
        //退出Excel
        excel.Quit();
    }
    public void addSheet(string Path, DataSet ds, string sheet)
    {
        SLDocument SlDoc = new SLDocument(Path);  
        SlDoc.SelectWorksheet(sheet);
        //SlDoc.RenameWorksheet("工作表1", sheet);
        //SlDoc.AddWorksheet(sheet);
        addData(SlDoc, ds.Tables[0]);
        SlDoc.SaveAs(Path);
    }
    public void addData(SLDocument SlDoc, DataTable dt)//對Excel的每列進行塞數據
    {
        var strCell = Columns(dt);

        for (int j = 0; j < dt.Columns.Count; j++)
        {
            SlDoc.SetCellValue(strCell[j] + 1, dt.Columns[j].ToString());//先塞列名
        }
        if (dt != null && dt.Rows.Count > 0)
        {
            for (int j = 0; j < dt.Rows.Count; j++)//按行進行對Excel塞table中數據
            {
                for (int i = 0; i < strCell.Length; i++)
                    SlDoc.SetCellValue(strCell[i] + (j + 2), dt.Rows[j][i].ToString());
            }
        }
    }
    public string[] Columns(DataTable dt)//獲取EXCEL的單元格的每行的列名A B C...
    {
        var num = 65;
        var str = new string[dt.Columns.Count];
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (i < 26)
            {
                str[i] = ((char)num).ToString();
            }
            else
            {
                var aa = (i / 26) + 64;

                var bb = num - (i / 26) * 26;
                str[i] = ((char)aa).ToString() + ((char)bb).ToString();
            }
            num++;
        }
        return str;
    }
    #endregion