首页 > 代码库 > 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