首页 > 代码库 > xmp
xmp
public static string ExportExcel(string tempfilename, Dictionary<string, DataTable> tables) { #region 导出Excel string destfilename = tempfilename.Substring(0, tempfilename.LastIndexOf(".")) + Guid.NewGuid().ToString() + DateTime.Now.ToString("MMddHHmmss") + ".xlsx"; File.Copy(tempfilename, destfilename); var source = File.ReadAllBytes(destfilename); using (SpreadsheetDocument document = SpreadsheetDocument.Open(destfilename, true)) { //1.去掉公式链 var chain = document.WorkbookPart.CalculationChainPart; document.WorkbookPart.DeletePart(chain); var sheets = document.WorkbookPart.Workbook.Descendants<Excel.Sheet>(); //2. 去掉所有的namerange var defineNames = document.WorkbookPart.Workbook.DefinedNames.Elements<Excel.DefinedName>(); foreach (Excel.Sheet sheet in sheets) { OpenXml.WorksheetPart part = (OpenXml.WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id); var excelrows = part.Worksheet.Descendants<Excel.Row>().ToList(); //2.去掉所有的formula foreach (var excelrow in excelrows) { var excelcells = excelrow.Descendants<Excel.Cell>(); foreach (var cell in excelcells) { if (cell.CellFormula != null) { cell.CellFormula = null; if (cell.CellValue != null) cell.CellValue.InnerXml = ""; } } } //F_开头的单元格 var fix = defineNames.Where(o => o.InnerText.IndexOf(sheet.Name) == 0 && o.Name.Value.IndexOf("F_") == 0); foreach (var f in fix) { string t = f.Name.Value.Substring(2); string[] d = t.Split(‘_‘); part.Worksheet.UpdateCell(Utility.GetColumnLetter(f.InnerText), (uint)Utility.GetRowIndex(f.InnerText), (tables[d[0]].Rows[0][d[1]] as string)) ; } //判断是否有动态 var dyn = defineNames.Where(o => o.InnerText.IndexOf(sheet.Name) == 0 && o.Name.Value.IndexOf("R_") == 0); if (dyn.Count() > 0) //Todo:这里只处理一个动态区 { var tb = dyn.First().Name.Value.Substring(2); var row = part.Worksheet.GetRow((uint)Utility.GetRowIndex(dyn.First().InnerText)); //Tddo:动态行只能是一行,不能是合并行 var dcells = defineNames.Where(o => o.InnerText.IndexOf(sheet.Name) == 0 && o.Name.Value.IndexOf("D_") == 0); for (int i = 0; i < tables[tb].Rows.Count; i++) { var newrow = part.Worksheet.CreateRow(row); //新增行 foreach (var c in dcells) { string t = c.Name.Value.Substring(2); string[] d = t.Split(‘_‘); newrow.UpdateCell( Utility.GetColumnLetter(c.InnerText), (tables[d[0]].Rows[i][d[1]] as string)); } } row.Hidden = new DocumentFormat.OpenXml.BooleanValue(true); //原来的模板行隐藏 } part.Worksheet.Save(); } //document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; //document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; document.WorkbookPart.Workbook.Save(); } #endregion return destfilename; }
static void Main(string[] args) { string appDir = System.Environment.CurrentDirectory; ExportExcel(appDir + "\\Template.xlsx", InitTable()); } public static Dictionary<string, DataTable> InitTable() { Dictionary<string, DataTable> tables = new Dictionary<string, DataTable>(StringComparer.OrdinalIgnoreCase); DataTable dt = new DataTable("User"); dt.Columns.Add("Name"); dt.Columns.Add("Age"); dt.Columns.Add("Birth"); var row = dt.NewRow(); dt.Rows.Add(row); row[0] = "张三"; row[1] = "25"; row[2] = "2014-4-12"; tables.Add(dt.TableName, dt); dt = new DataTable("Exp"); dt.Columns.Add("No"); dt.Columns.Add("Date"); dt.Columns.Add("Title"); row = dt.NewRow(); dt.Rows.Add(row); row[0] = "1"; row[1] = "2014.1.1-2014.2.1"; row[2] = "小组成员"; row = dt.NewRow(); dt.Rows.Add(row); row[0] = "2"; row[1] = "2014.3.1-2014.4.1"; row[2] = "组长"; row = dt.NewRow(); dt.Rows.Add(row); row[0] = "3"; row[1] = "2014.5.1-2014.6.1"; row[2] = "总经理"; tables.Add(dt.TableName, dt); return tables; }
xmp
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。