C# XLWorkbook.Save方法代码示例

        public static void Execute(DownloadServiceOptions options)
        {
            using (var workbook = new XLWorkbook(options.File))
            using (var worksheet = workbook.Worksheet(1))
            {
                var fullRange = worksheet.RangeUsed();
                var rangeUsed = worksheet.Range(2, 1, fullRange.RangeAddress.LastAddress.RowNumber, fullRange.RangeAddress.LastAddress.ColumnNumber);
                const int movieNameCell = 1;
                const int yearCell = 2;
                const int processedCell = 3;
                const int downloadedCell = 4;

                var fullCount = 0;

                foreach (var row in rangeUsed.RowsUsed())
                {
                    if (fullCount > 100) break;
                    // must have 4 cells, and the processed cell must not contain an x
                    if (row.CellCount() == 4 && !row.Cell(processedCell).GetValue<string>().Equals("x"))
                    {
                        var urls = CreateUrl(options.BaseUrl, row.Cell(movieNameCell).GetValue<string>(), row.Cell(yearCell).GetValue<string>());
                        var downloaded = false;
                        foreach (var url in urls)
                        {
                            Thread.Sleep(1000 * 30); // wait 30 seconds

                            var hash = GetTorrentMagnetHash(PullSource(url));
                            if (string.IsNullOrEmpty(hash)) continue;

                            var torrent = string.Format("http://torcache.net/torrent/{0}.torrent", hash);
                            var result = DownloadTorrent(torrent, options.DownloadPath);

                            if (!result) continue;

                            downloaded = true;
                            break;
                        }
                        row.Cell(processedCell).SetValue("x");
                        if (downloaded)
                            row.Cell(downloadedCell).SetValue("x");
                        fullCount++;
                    }
                }

                workbook.Save();
            }
        }

        // Public
        public void Create(String filePath)
        {
            #region Create case
            {
                var workbook = new XLWorkbook();
                var ws = workbook.Worksheets.Add("Delete red rows");

                // Put a value in a few cells
                foreach (var r in Enumerable.Range(1, 5))
                    foreach (var c in Enumerable.Range(1, 5))
                        ws.Cell(r, c).Value = string.Format("R{0}C{1}", r, c);

                var blueRow = ws.Rows(1, 2);
                var redRow = ws.Row(5);

                blueRow.Style.Fill.BackgroundColor = XLColor.Blue;

                redRow.Style.Fill.BackgroundColor = XLColor.Red;
                workbook.SaveAs(filePath);
            }
            #endregion

            #region Remove rows
            {
                var workbook = new XLWorkbook(filePath);
                var ws = workbook.Worksheets.Worksheet("Delete red rows");

                ws.Rows(1, 2).Delete();
                workbook.Save();
            }
            #endregion
        }

        public void Run(string fileSpese, string fileAnalisi, string template)
        {
            var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2011;
            var anni = Enumerable.Range(2011, numeroDiAnniFinoAdOggi + 1);

            File.Copy(template, fileAnalisi, true);

            using (var analisiWb = new XLWorkbook(fileAnalisi))
            {
                var dati = analisiWb.Worksheet("Dati");
                int riga = 2;

                using (var spese = new XLWorkbook(fileSpese))
                {
                    foreach (var anno in anni)
                    {
                        var wsAnno = spese.Worksheet(anno + "");
                        var movimenti = wsAnno.Rows().Select(Movimento.TryParse).Where(r => r != null).ToArray();

                        foreach (var movimento in movimenti)
                        {
                            dati.Cell(riga, "A").Value = movimento.Data;
                            dati.Cell(riga, "B").Value = movimento.Categoria;
                            dati.Cell(riga, "C").Value = movimento.Descrizione;
                            dati.Cell(riga, "D").Value = movimento.Spesa;
                            riga++;
                        }

                    }

                    var dataAsTable = dati.RangeUsed().AsTable();
                    dati.Tables.Add(dataAsTable);


                    //var range = dati.Range("B1", "B30");

                    //var pivotSh = analisiWb.Worksheet("Pivot");
                    //pivotSh.PivotTable("PivotTable1").SetRefreshDataOnOpen(true);

                }


                analisiWb.Save();

            }
        }

 public static void UpdateCompetitionArchive(string excelFileName, Competition c)
 {
     try
       {
     XLWorkbook xLWorkbook = new XLWorkbook(excelFileName);
     CreateScheduleStylesheet(c, xLWorkbook, true);
     xLWorkbook.Save();
       }
       catch (Exception ex)
       {
     if (ex.Message.Contains("being used"))
       MessageBox.Show("Zatvori arhivu pa pokusaj opet!");
     else
       MessageBox.Show("Greska!");
       }
 }

        /// <summary>
        /// 엑셀 오류메세지 쓰기
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="errList"></param>
        /// <returns></returns>
        public bool WriteExcelByErrMsg(string filePath, IList<string> errList)
        {
            bool isSuccess = true;
            try
            {
                XLWorkbook wb = new XLWorkbook(filePath);
                IXLWorksheet worksheet = wb.Worksheets.First();
                int colCount = worksheet.RangeUsed().ColumnCount();

                for (int i = 0; i < errList.Count; i++)
                {
                    worksheet.Cell(i + 1, colCount + 1).SetValue(errList[i]);
                }
                wb.Save();
            }
            catch (Exception ex)
            {
                isSuccess = false;
            }
            return isSuccess;
        }

        static Boolean file_processing(string file_patch, string file_name)
        {
            string par = "\\";

            if (file_name == "argument_check")
            {
                par = "";
                file_name = "";
            }

            var wb = new XLWorkbook(file_patch + par + file_name);

            var ws = wb.Worksheets.Worksheet(1);

            ws.PageSetup.Margins.Top = 0.208;
            ws.PageSetup.Margins.Bottom = 0.208;
            ws.PageSetup.Margins.Left = 0.416;
            ws.PageSetup.Margins.Right = 0.208;
            ws.PageSetup.Margins.Footer = 0.333;
            ws.PageSetup.Margins.Header = 0.333;

            wb.SaveAs(file_patch + par + file_name);

            if (ws.Cell(16, 1).Value.ToString() == "Инвентаризационная опись")
            {
                WriteLineColor("Файл " + file_name + " уже конвертирован!", "Red");
                Log.log_write("Файл " + file_name + " уже конвертирован!", "WARNING", "warning");
                already_con++;
                return false;
            }

            if (ws.Cell(2, 1).Value.ToString() != "Инвентаризационная опись")
            {
                WriteLineColor("Файл " + file_name + " формат не подходит!", "Red");
                Log.log_write("Файл " + file_name + " формат не подходит!", "ERROR", "warning");
                failed++;
                return false;
            }

            if (backup_data(file_patch, file_name))
            {

                WriteLineColor("Создана копия оригинального файла.", "Magenta");
            }
            else
            {
                WriteLineColor("Внимание копия файла не сделана!", "Red");
                Console.ReadKey();
            }

            WriteLineColor("Обработка...", "Cyan");

            //Удаляем ненужные колонки

            ws.Column(9).Delete();
            ws.Range("A1:I5").Delete(XLShiftDeletedCells.ShiftCellsLeft);

            ws.Range("A7:I11").Delete(XLShiftDeletedCells.ShiftCellsLeft);

            ws.Range("A11:I15").Delete(XLShiftDeletedCells.ShiftCellsLeft);

            ws.Range("A15:I17").Delete(XLShiftDeletedCells.ShiftCellsLeft);

            //Прячем ненужные строки
            int i = 1;

            while (i <= 15)
            {
                ws.Row(i).Hide();
                i++;
            }

            //Объединение ячеек
            ws.Range("A16:G16").Row(1).Merge();
            ws.Range("A17:G17").Row(1).Merge();

            ws.Cell(16, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            ws.Cell(16, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            ws.Cell(16, 1).Value = "Инвентаризационная опись № " + file_name.ToLower().Replace("c", "").Replace("с", "").Replace("_", "").Replace(".xlsx", "");

            ws.Cell(17, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            ws.Cell(17, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            ws.Cell(17, 1).Value = "товарно-материальных ценностей";

            int total_cell = 16;
            int total_coll = 1;

            while (total_coll <= 7)
            {
                total_cell = 16;

                while (ws.Cell(total_cell, 1).GetString() != "")
                {
                    ws.Cell(total_cell, total_coll).Style.Font.FontName = "Arial";

                    ws.Cell(total_cell, total_coll).Style.Font.FontSize = 11;

                    total_cell++;
                }

//.........这里部分代码省略.........

//.........这里部分代码省略.........
                ws.Cell(start_cell, 4).Style.Border.BottomBorderColor = XLColor.Black;

                ws.Cell(start_cell, 7).Style.Border.LeftBorder = XLBorderStyleValues.Hair;
                ws.Cell(start_cell, 7).Style.Border.RightBorder = XLBorderStyleValues.Hair;
                ws.Cell(start_cell, 7).Style.Border.BottomBorder = XLBorderStyleValues.Hair ;
                ws.Cell(start_cell, 7).Style.Border.BottomBorderColor = XLColor.Black;

                start_cell++;
            }

            //очищаем линию в конце документа
            int i =1;

            while (i < 8)
            {
                ws.Cell(total_cell-1, i).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
                ws.Cell(total_cell-1, i).Style.Border.BottomBorderColor = XLColor.Black;

                i++;
            }

            //очищяем конец документа для добавления доп.строк
            i = 1;

            while (i <= 9)
            {
                ws.Range("A" + (total_cell + i).ToString() + ":I" + (total_cell + i).ToString()).Delete(XLShiftDeletedCells.ShiftCellsLeft);
                i++;
            }

            //добавление 10 дополнительных строк для неучтенных сразу строк
            i = 0;

            while (i <= 10)
            {
                int z = 1;

                while (z < 8)
                {
                    ws.Cell(total_cell + i, z).Style.Border.LeftBorder = XLBorderStyleValues.Hair;
                    ws.Cell(total_cell + i, z).Style.Border.RightBorder = XLBorderStyleValues.Hair;
                    ws.Cell(total_cell + i, z).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
                    ws.Cell(total_cell + i, z).Style.Border.BottomBorderColor = XLColor.Black;

                    z++;
                }

                i++;
            }

            //Линия обозначающая конец акта
            i = 1;

            while (i < 8)
            {
                ws.Cell(total_cell + 11, i).Style.Border.TopBorder = XLBorderStyleValues.Medium;
                ws.Cell(total_cell + 11, i).Style.Border.BottomBorderColor = XLColor.Black;

                i++;
            }

            int first_insert = total_cell + 12;

            ws.Cell(first_insert, 1).Value = "Всего наименований:";
            ws.Cell(first_insert , 1).Style.Font.FontName = "Arial";
            ws.Cell(first_insert , 1).Style.Font.FontSize = 11;
            ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();

            first_insert = total_cell + 14;

            ws.Cell(first_insert, 1).Value = "Всего единиц товара:";
            ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
            ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
            ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();

            first_insert = total_cell + 16;

            ws.Cell(first_insert, 1).Value = "На сумму:";
            ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
            ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
            ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();

            first_insert = total_cell + 18;

            ws.Cell(first_insert, 1).Value = "Материально-ответственное(ые) лицо(а) :";
            ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
            ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
            ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();

            first_insert = total_cell + 20;

            ws.Cell(first_insert, 1).Value = "Начальник комиссии :";
            ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
            ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
            ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();

            wb.Save();

            return true;
        }

        public void _openExcel(String dashboardFile, String metricsFolder)
        {
            //grab all the files in the metrics folder
            metrics_files = Directory.GetFiles(metricsFolder);

            if (File.Exists("Copy.xlsx"))
            {
                File.Delete("Copy.xlsx");
            }
            File.Copy(dashboardFile, "Copy.xlsx");

            //try openeing the excel sheets listed in metricsFile
            try
            {
                _dashboard = new XLWorkbook(dashboardFile);

                //iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then  extrract data from
                //metrics reports and insert into those sheets
                int numSheets = _dashboard.Worksheets.Count;
                for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                    {
                        var sheet = _dashboard.Worksheet(sheetNum);
                        prepDashboardSheets(sheet);
                     }
                _dashboard.Save();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }