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
本文来自博客园,作者:码农阿亮,转载请注明原文链接:https://www.cnblogs.com/wml-it/p/15126955.html
技术的发展日新月异,随着时间推移,无法保证本博客所有内容的正确性。如有误导,请大家见谅,欢迎评论区指正!
开源库地址,欢迎点亮:
GitHub:https://github.com/ITMingliang
Gitee: https://gitee.com/mingliang_it
GitLab: https://gitlab.com/ITMingliang
建群声明: 本着技术在于分享,方便大家交流学习的初心,特此建立【编程内功修炼交流群】,为大家答疑解惑。热烈欢迎各位爱交流学习的程序员进群,也希望进群的大佬能不吝分享自己遇到的技术问题和学习心得!进群方式:扫码关注公众号,后台回复【进群】。
![](https://images.cnblogs.com/cnblogs_com/wml-it/2196906/o_230823011905_wxgzhss.png)