C# Workbook.SaveDocument方法代码示例

 public void RunReport()
 {
     using (Workbook Wbk = new Workbook())
     {
         Wbk.LoadDocument(FilePath);
         Worksheet ShtMain = GetWorkSheet(Wbk);
         ImportData(ShtMain);
         Wbk.SaveDocument(FilePath, DocumentFormat.Xls);
     }
 }

 public void RunReport()
 {
     using (Workbook Wbk = new Workbook())
     {
         Wbk.LoadDocument(FilePath);
         Worksheet ShtMain = Wbk.Worksheets["主页"];
         Dictionary<string, Item> DictItem = GetItem(ShtMain);
         Worksheet ShtBomDetail = Wbk.Worksheets.Add("采购资料");
         InitBomDetailSheet(ShtBomDetail);
         ListAllBomLinesToSheet(DictItem, ShtBomDetail);
         Wbk.SaveDocument(FilePath, DocumentFormat.Xls);
     }
 }

        private ActionResult ConvertToExcelFile(OperationDataParamConfigurationViewModel viewModel,
                                                OperationDataConfigurationViewModel data)
        {
            var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, ConfigType.OperationData));
            if (!Directory.Exists(resultPath))
            {
                Directory.CreateDirectory(resultPath);
            }

            string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString();
            string dateFormat = string.Empty;
            switch (viewModel.PeriodeType)
            {
                case "Yearly":
                    dateFormat = "yyyy";
                    break;
                case "Monthly":
                    dateFormat = "mmm-yy";
                    workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year);
                    break;
                default:
                    dateFormat = "dd-mmm-yy";
                    workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year,
                                                  viewModel.Month.ToString().PadLeft(2, '0'));
                    break;
            }

               string fileName = string.Format(@"{0}.xlsx", DateTime.Now.ToString("yyyymmddMMss"));
            /*string fileName = new StringBuilder(guid).Append(".xlsx").ToString();*/

            IWorkbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

            worksheet.Name = workSheetName;
            workbook.Worksheets.ActiveWorksheet = worksheet;
            RowCollection rows = workbook.Worksheets[0].Rows;
            ColumnCollection columns = workbook.Worksheets[0].Columns;

            Row headerRow = rows[0];
            headerRow.FillColor = Color.DarkGray;
            headerRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            headerRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            Column kpiIdColumn = columns[0];
            Column kpiNameColumn = columns[1];
            kpiIdColumn.Visible = false;

            headerRow.Worksheet.Cells[headerRow.Index, kpiIdColumn.Index].Value = "KPI ID";
            headerRow.Worksheet.Cells[headerRow.Index, kpiNameColumn.Index].Value = "KPI Name";

            int i = 1;
            foreach (var kpi in data.Kpis)
            {
                int j = 2;
                worksheet.Cells[i, kpiIdColumn.Index].Value = kpi.Id;
                worksheet.Cells[i, kpiNameColumn.Index].Value = string.Format(@"{0} ({1})", kpi.Name, kpi.MeasurementName);

                foreach (var operationData in kpi.OperationData.OrderBy(x => x.Periode))
                {
                    worksheet.Cells[headerRow.Index, j].Value = operationData.Periode;
                    worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[headerRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = operationData.Value;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }

                Column totalValueColumn = worksheet.Columns[j];
                if (i == headerRow.Index + 1)
                {
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
                    Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                }
                else
                {
                    // add formula
                    Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                }

                i++;
            }

            kpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);

            string resultFilePath = string.Format("{0},{1}", resultPath, fileName);

            using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                stream.Close();
            }

            string namafile = Path.GetFileName(resultFilePath);
//.........这里部分代码省略.........

        private string _ExportToExcel(ConfigurationKpiAchievementsViewModel viewModel)
        {
            string dateFormat = "dd-mmm-yy";
            string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString();
            switch (viewModel.PeriodeType)
            {
                case "Yearly":
                    dateFormat = "yyyy";
                    break;
                case "Monthly":
                    dateFormat = "mmm-yy";
                    workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year);
                    break;
                default:
                    dateFormat = "dd-mmm-yy";
                    workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year, viewModel.Month.ToString().PadLeft(2, '0'));
                    break;
            }
            string fileName = new StringBuilder(workSheetName).Append(".xls").ToString();
            string resultFilePath = System.Web.HttpContext.Current.Request.MapPath(TemplateDirectory + fileName);
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

            worksheet.Name = workSheetName;
            workbook.Worksheets.ActiveWorksheet = worksheet;

            RowCollection rows = workbook.Worksheets[0].Rows;
            ColumnCollection columns = workbook.Worksheets[0].Columns;

            Row HeaderRow = rows[0];
            HeaderRow.FillColor = Color.DarkGray;
            HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            Column KpiIdColumn = columns[0];
            Column KpiNameColumn = columns[1];
            KpiIdColumn.Visible = false;

            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
            int i = 1; //i for row
            foreach (var kpi in viewModel.Kpis)
            {
                worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
                worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
                int j = 2; // for column

                foreach (var achievement in kpi.KpiAchievements)
                {
                    worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
                    worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = achievement.Value;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }
                Column TotalValueColumn = worksheet.Columns[j];
                if (i == HeaderRow.Index + 1)
                {
                    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
                    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
                    Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                }
                else
                {
                    // add formula
                    Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                }
                i++;
            }

            KpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);

            workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
            //todo create file from viewModel
            return string.Format("{0}{1}", TemplateDirectory, fileName);
        }

//.........这里部分代码省略.........
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }

                    case "KpiAchievement":
                        {
                            foreach (var achievement in kpi.KpiAchievements)
                            {
                                var item = new ConfigurationViewModel.Item()
                                {
                                    Id = achievement.Id,
                                    KpiId = kpi.Id,
                                    Periode = achievement.Periode,
                                    Remark = achievement.Remark,
                                    Value = achievement.Value.HasValue ? achievement.Value.ToString() : string.Empty,
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }

                    case "OperationData":
                        {
                            //items = kpi.OperationData.MapTo<ConfigurationViewModel.Item>();
                            foreach (var operationData in kpi.OperationData)
                            {
                                var item = new ConfigurationViewModel.Item()
                                {
                                    Id = operationData.Id,
                                    KpiId = kpi.Id,
                                    Periode = operationData.Periode,
                                    Remark = operationData.Remark,
                                    Value = operationData.Value.HasValue ? operationData.Value.ToString() : string.Empty,
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }
                }

                foreach (var item in items)
                {
                    worksheet.Cells[headerRow.Index, j].Value = item.Periode;
                    worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[headerRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = item.RealValue;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }

                Column totalValueColumn = worksheet.Columns[j];
                if (i == headerRow.Index + 1)
                {
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
                    Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                }
                else
                {
                    // add formula
                    Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                }
                i++;
            }
            #endregion
            kpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);

            string resultFilePath = string.Format("{0},{1}", resultPath, fileName);
            //System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));

            using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                stream.Close();
            }
            //System.Web.HttpContext.Current.Response.End();
            //workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
            //workbook.Dispose();
            #endregion

            string namafile = Path.GetFileName(resultFilePath);
            byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
            var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = fileName };
            return response;
        }

        private static byte[] CreateSpreadsheetReport(IList<QueryReportDataGroup> reportData)
        {
            using (var workbook = new Workbook())
            {
                workbook.BeginUpdate();
                var sheet = workbook.Worksheets.First();
                sheet.Name = "Запросы";

                var i = 0;
                var j = 1;
                foreach (var pair in reportData.First().Values) //шапка по горизонтали
                {
                    var cell = sheet.Cells[i, j++];
                    cell.Value = pair.Key.title;
                    cell.FillColor = Colors.Accent;
                    cell.Font.Color = Color.White;
                    cell.Borders.LeftBorder.Color = Color.White;
                    cell.Borders.RightBorder.Color = Color.White;
                    cell.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
                }
                j = 0;
                var grouped = reportData.GroupBy(t =>
                {
                    if (t.GroupHead == t.Head)
                        return String.Empty;
                    return t.GroupHead;
                });
                foreach (var group in grouped) //группы по вертикали
                {
                    j = 0;
                    i++;

                    var groupRange = sheet.Range.FromLTRB(0, i, reportData.First().Values.Count, i);
                    sheet.MergeCells(groupRange, MergeCellsMode.ByColumns);
                    groupRange.ExistingCells.First().Value = group.Key;
                    groupRange.Borders.BottomBorder.LineStyle = BorderLineStyle.Medium;
                    groupRange.Font.FontStyle = SpreadsheetFontStyle.Bold;
                    groupRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
                    
                    foreach (var data in group) //данные по вертикали
                    {
                        j = 0;
                        i++;
                        
                        sheet.Cells[i, j++].Value = data.Head;
                        foreach (var pair in data.Values) //запрос-значение по горизонтали
                        {
                            var cell = sheet.Cells[i, j++];
                            cell.Value = pair.Value.ToString();
                            cell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
                            cell.NumberFormat = "@";
                        }
                    }
                }
                var reportRange = sheet.Range.FromLTRB(0, 0, reportData.First().Values.Count, i);
                reportRange.Borders.SetAllBorders(Colors.Accent, BorderLineStyle.Thin);
                reportRange.AutoFitColumns();
                reportRange.ColumnWidth = reportRange.ColumnWidth * 0.5;
                reportRange.Alignment.WrapText = true;
                reportRange.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
                reportRange.AutoFitRows();
                workbook.EndUpdate();
                using (var stream = new MemoryStream())
                {
                    workbook.SaveDocument(stream, DocumentFormat.Xlsx);
                    return stream.ToArray();
                }
            }
        }

//.........这里部分代码省略.........
            ColumnCollection columns = workbook.Worksheets[0].Columns;

            Row HeaderRow = rows[0];
            HeaderRow.FillColor = Color.DarkGray;
            HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            Column KpiIdColumn = columns[0];
            Column KpiNameColumn = columns[1];
            KpiIdColumn.Visible = false;

            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
            int i = 1; //i for row
            foreach (var kpi in viewModel.Kpis)
            {
                worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
                worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
                int j = 2; // for column
                var items = new List<ConfigurationViewModel.Item>();
                switch (configType)
                {
                    case "KpiTarget":
                        foreach (var target in kpi.KpiTargets)
                        {
                            var item = new ConfigurationViewModel.Item();
                            item.Id = target.Id;
                            item.KpiId = kpi.Id;
                            item.Periode = target.Periode;
                            item.Remark = target.Remark;
                            item.Value = target.Value;
                            item.PeriodeType = pType;
                            items.Add(item);
                        }
                        break;
                    case"KpiAchievement":
                        foreach (var achieve in kpi.KpiAchievements)
                        {
                            var item = new ConfigurationViewModel.Item() { Id = achieve.Id, KpiId = achieve.Id, Periode = achieve.Periode, Remark = achieve.Remark, Value = achieve.Value, PeriodeType = pType };
                            items.Add(item);
                        }
                        break;
                    case "Economic":
                        items = kpi.Economics.MapTo<ConfigurationViewModel.Item>();
                        break;
                    default:
                        break;
                }
                foreach (var achievement in items)
                {
                    worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
                    worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = achievement.Value;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }
                //Column TotalValueColumn = worksheet.Columns[j];
                //if (i == HeaderRow.Index + 1)
                //{
                //    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
                //    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
                //    Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                //    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                //    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                //}
                //else
                //{
                //    // add formula
                //    Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                //    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                //    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                //}
                i++;
            }

            KpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);

            string resultFilePath = string.Format("{0},{1}",resultPath,fileName);// System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));

            using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.SaveDocument(stream, DocumentFormat.Xlsx);
                stream.Close();
            }
            //System.Web.HttpContext.Current.Response.End();
            //workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
            //workbook.Dispose();
            #endregion

            string namafile = Path.GetFileName(resultFilePath);
            byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
            var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = namafile };
            return response;
        }