Beispiel #1
0
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="filePath">导出的文件路径</param>
        /// <param name="fileTemplatePath">模板路径</param>
        /// <exception cref="Exception">
        /// </exception>
        public void ExcelExport(DataSet ds, string filePath, string fileTemplatePath)
        {
            try
            {
                System.IO.File.Copy(fileTemplatePath, filePath);
            }
            catch (Exception ex)
            {
                throw new Exception("复制Excel文件出错" + ex.Message);
            }

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
            {
                var sheetExs = document.GetWorksheets();
                for (int s = 0; s < sheetExs.Count; s++)
                {
                    if (s >= ds.Tables.Count)
                    {
                        break;
                    }
                    DataTable dt = ds.Tables[s];
                    if (dt == null || dt.Rows.Count < 1)
                    {
                        continue;
                    }
                    List <string> listCol = new List <string>();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        listCol.Add(dc.ColumnName);
                    }
                    var sheetEx   = sheetExs[s];
                    var sheetData = sheetEx.Worksheet.GetFirstSheetData();
                    OpenXml.CellStyleIndex = 1;
                    ////写标题相关信息
                    //this.UpdateTitleText(sheetData);

                    int startRowIndex = 0;
                    if (RowIndex != null && RowIndex.Length > 0)
                    {
                        startRowIndex = RowIndex[s];
                    }
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        var     rowIndex = startRowIndex + i;
                        DataRow dr       = dt.Rows[i];
                        for (int l = 0; l < listCol.Count; l++)
                        {
                            string cellName = IntToMoreChar(l + 1) + rowIndex;
                            sheetData.SetCellValue(cellName, dr[listCol[l]]);
                        }
                    }

                    if (OpenXmlExportImages != null && OpenXmlExportImages.Count > 0 && OpenXmlExportImages.ContainsKey(sheetEx.SheetName))
                    {
                        var exportImages = OpenXmlExportImages[sheetEx.SheetName];
                        if (exportImages != null)
                        {
                            foreach (var img in exportImages)
                            {
                                document.InsertImage(sheetEx.SheetId, img.X, img.Y, img.Width, img.Height, img.ImagePath);
                            }
                        }
                    }
                }
                // var str = OpenXmlHelper.ValidateDocument(document);验证生成的Excel
            }
        }