private void AddBorders(int rowIndex)
 {
     _worksheet.Range(rowIndex, 1, rowIndex + 2, 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
     _worksheet.Range(rowIndex, 2, rowIndex + 2, 2).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
     _worksheet.Range(rowIndex, 3, rowIndex + 2, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
     _worksheet.Range(rowIndex, 4, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
     .InsideBorder = XLBorderStyleValues.Thin;
     _worksheet.Range(rowIndex, 4, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
     .OutsideBorder = XLBorderStyleValues.Thin;
     _worksheet.Range(rowIndex, 1, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
     .OutsideBorder = XLBorderStyleValues.Medium;
 }
Esempio n. 2
0
 /// <summary>
 /// Apply formatting to the worksheet
 /// </summary>
 /// <param name="worksheet">The worksheet to format</param>
 private void FormatWorksheet(ref IXLWorksheet worksheet)
 {
     worksheet.ColumnsUsed().AdjustToContents();
     worksheet.SheetView.FreezeRows(1);
     worksheet.Rows().Height = 30;
     worksheet.Column(3).Width = 40;
 }
        internal void CreateWorksheet(IXLWorkbook workbook, string sheetName, IElement tableNode)
        {
            IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);

            int row = 1;
            int col = 1;

            foreach (IElement rowNode in tableNode.QuerySelectorAll("tr"))
            {
                List <IElement> cells = rowNode.QuerySelectorAll("th").ToList();
                cells.AddRange(rowNode.QuerySelectorAll("td"));
                foreach (IElement cellNode in cells)
                {
                    RenderCell(worksheet, cellNode, row, ref col);
                }
                col = 1;
                row++;
            }

            if (!hasMergedCells)
            {
                var table = worksheet.RangeUsed().CreateTable("mainTable" + worksheet.Name);
                table.Theme          = XLTableTheme.TableStyleLight1;
                table.ShowRowStripes = Settings.ShowRowStripes;
                table.ShowAutoFilter = Settings.ShowFilter;
            }

            if (Settings.AutofitColumns)
            {
                worksheet.ColumnsUsed().AdjustToContents();
            }
        }
Esempio n. 4
0
        private void makeCustomer(List <Customer> data, IXLWorksheet sheet)
        {
            sheet.Cell(1, 1).Value = "客戶資料(" + data.Count() + ")";
            int row_index = 2;

            #region 主標題
            sheet.Cell(row_index, 1).Value = "[客戶姓名]";
            sheet.Cell(row_index, 2).Value = "[email]";
            sheet.Cell(row_index, 3).Value = "[電話]";
            sheet.Cell(row_index, 4).Value = "[手機]";
            sheet.Cell(row_index, 5).Value = "[郵遞區號]";
            sheet.Cell(row_index, 6).Value = "[地址]";

            setFontColor_Label(sheet, row_index, row_index, 1, 6, XLColor.Blue);
            row_index++;
            #endregion

            foreach (var i in data)
            {
                sheet.Cell(row_index, 1).Value = i.c_name;
                sheet.Cell(row_index, 2).Value = i.email;
                sheet.Cell(row_index, 3).Value = i.tel;
                sheet.Cell(row_index, 4).Value = i.mobile;
                sheet.Cell(row_index, 5).Value = i.zip;
                sheet.Cell(row_index, 6).Value = i.address;
                row_index++;
            }
            sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
        }
Esempio n. 5
0
        private static string GetCreateTableQuery(IXLWorksheet workSheet)
        {
            var createStatement = "CREATE TABLE FEATURES";

            foreach (var col in workSheet.ColumnsUsed())
            {
                var guessedType = GuessType(col);
            }
            var columnDeclarations = workSheet.ColumnsUsed()
                                     .Select(col =>
                                             Regex.Replace(col.FirstCellUsed().Value.ToString(), "[^0-9a-zA-Z]+", "") +
                                             " " +
                                             GuessType(col).GetSqlDataType())
                                     .Aggregate((current, next) => $"{current}, {next}");

            return(string.Format("{0} ({1})", createStatement, columnDeclarations));
        }
Esempio n. 6
0
        public string[,] ConvertWorksheetTo2dArray(IXLWorksheet worksheet)
        {
            // Look for the first row used
            var firstRowUsed = worksheet.FirstRowUsed();

            // Narrow down the row so that it only includes the used part
            var residentinfoHeaders = firstRowUsed.RowUsed();
            var residentinfo        = residentinfoHeaders;

            Console.WriteLine($"Show Resident Information: ", residentinfoHeaders.Cell(1).Value);

            var i = 1;

            while (!residentinfo.Cell(i).IsEmpty())
            {
                //Console.WriteLine();
                Console.WriteLine(residentinfo.Cell(i).Value + "|");
                i++;
            }



            // for(InitializeSomething; BooleanCheckSomething; MutateSomething(s))
            //
            //

            for (var row = firstRowUsed.RowUsed(); !row.IsEmpty(); row = row.RowBelow())
            {
                foreach (var cell in row.Cells())
                {
                    Console.Write(cell.Value);
                    Console.Write(" ");
                }
                Console.WriteLine();
            }


            //Index's for The Cells in Table
            var activeRowCount    = worksheet.RowsUsed().Count();
            var activeColumnCount = worksheet.ColumnsUsed().Count();

            string[,] my2dArrayWithConvertedCells = new string[, ]
            {
                { "", "" },
            };


            //using foreach to create 2d Array
            //convert to 2d Array and return the values
            var rangeOfFirstNames = worksheet.Range("A1:A5");

            foreach (var cell in rangeOfFirstNames.Cells())
            {
                cell.SetDataType(XLDataType.Text);
                var valueofcell = cell.Value.ToString();
            }
            return(my2dArrayWithConvertedCells);
        }
Esempio n. 7
0
        public static void writeResult(Result result)
        {
            File.Copy(OutputPath, OutputPath + ".bak.xlsm", true);

            XLWorkbook   workbook  = new XLWorkbook(OutputPath);
            IXLWorksheet worksheet = workbook.Worksheet(1);

            int resultColumn = worksheet.ColumnsUsed().Count();

            foreach (var item in result)
            {
                ++resultColumn;
                var cfgResult          = item.Value;
                int bestObjStartRow    = BestObjStartRow;
                int averageObjStartRow = AverageObjStartRow;

                worksheet.Cell(bestObjStartRow++, resultColumn).Value    = item.Key;
                worksheet.Cell(averageObjStartRow++, resultColumn).Value = item.Key;

                worksheet.Cell(bestObjStartRow++, resultColumn).Value    = cfgResult.First().Value.startDate;
                worksheet.Cell(averageObjStartRow++, resultColumn).Value = cfgResult.First().Value.startDate;

                // EXTEND[szx][5]: add RunTime row (more run time can compensate bad configuration which may confuse the comparison).

                int feasibleCount = 0;
                int runCount      = 0;
                for (int i = 0; i < InstanceNum; ++i)
                {
                    string instanceName = worksheet.Cell(BestObjStartRow + HeaderRowNum + i, InstanceNameColumn).GetString();
                    if (!cfgResult.ContainsKey(instanceName))
                    {
                        continue;
                    }
                    feasibleCount += cfgResult[instanceName].feasibleCount;
                    runCount      += cfgResult[instanceName].runCount;
                }
                worksheet.Cell(bestObjStartRow++, resultColumn).Value    = feasibleCount;
                worksheet.Cell(averageObjStartRow++, resultColumn).Value = feasibleCount;
                worksheet.Cell(bestObjStartRow++, resultColumn).Value    = runCount;
                worksheet.Cell(averageObjStartRow++, resultColumn).Value = runCount;

                worksheet.Cell(bestObjStartRow++, resultColumn).SetFormulaR1C1(NormalizedScoreFormula);
                worksheet.Cell(averageObjStartRow++, resultColumn).SetFormulaR1C1(NormalizedScoreFormula);

                for (int i = 0; i < InstanceNum; ++i)
                {
                    string instanceName = worksheet.Cell(bestObjStartRow + i, InstanceNameColumn).GetString();
                    if (!cfgResult.ContainsKey(instanceName))
                    {
                        continue;
                    }
                    worksheet.Cell(bestObjStartRow + i, resultColumn).Value    = cfgResult[instanceName].bestObj;
                    worksheet.Cell(averageObjStartRow + i, resultColumn).Value = cfgResult[instanceName].AverageObj;
                }
            }

            workbook.Save();
        }
Esempio n. 8
0
        private void makeShip(List <Api.PurchaseController.ShipData> data, IXLWorksheet sheet)
        {
            int row_index = 2;

            #region 產品名稱
            sheet.Cell(row_index, 3).Value = "咖啡豆重量";
            List <string> p_name    = data.Select(x => x.p_name).ToList();
            int           col_index = 4;
            foreach (var i in p_name)
            {
                sheet.Cell(row_index, col_index).Value                = i;
                sheet.Cell(row_index, col_index).Style.Font.Bold      = true;
                sheet.Cell(row_index, col_index).Style.Font.FontColor = XLColor.FromArgb(153, 51, 0);
                col_index++;
            }
            sheet.Cell(1, 2).Value = "訂單未出貨統計";
            sheet.Range(1, 2, 1, col_index).Merge();
            setFontColorAndBg(sheet, 1, 2, XLColor.White, XLColor.FromArgb(156, 101, 0));
            int col_end = col_index;
            row_index++;
            #endregion
            col_index = 4;
            foreach (var i in data)
            {
                string name = sheet.Cell(2, col_index).GetValue <string>();
                if (i.p_name == name)
                {
                    foreach (var j in i.Detail)
                    {
                        sheet.Cell(row_index, 1).Value         = j.purchase_no;   //訂單編號
                        sheet.Cell(row_index, 2).Value         = j.p_d_pack_name; //訂單編號
                        sheet.Cell(row_index, 3).Value         = j.weight;        //咖啡豆重量
                        sheet.Cell(row_index, col_index).Value = j.qty;           //數量
                        row_index++;
                    }
                    col_index++;
                }
            }
            #region  方加總
            sheet.Cell(row_index, 2).Value = "未出貨統計(g)";
            sheet.Range(row_index, 2, row_index, 3).Merge();
            setFontColorAndBg(sheet, row_index, 2, XLColor.FromArgb(153, 51, 0), XLColor.FromArgb(255, 235, 156));
            for (var i = 4; i < col_end; i++)
            {
                sheet.Cell(row_index, i).FormulaA1                  = string.Format("SUMPRODUCT(C{0}:C{1},{2}{0}:{2}{1})", 3, row_index - 1, Convert.ToChar(64 + i));
                sheet.Cell(row_index, i).Style.Font.FontColor       = XLColor.FromArgb(156, 101, 0);
                sheet.Cell(row_index, i).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
            }
            sheet.Cell(row_index, col_end).FormulaA1                  = string.Format("SUM({0}{1}:{2}{1})", Convert.ToChar(64 + 4), row_index, Convert.ToChar(63 + col_end));
            sheet.Cell(row_index, col_end).Style.Font.FontColor       = XLColor.FromArgb(156, 101, 0);
            sheet.Cell(row_index, col_end).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
            #endregion
            sheet.Range(1, 2, row_index, col_end).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
            sheet.Range(1, 2, row_index, col_end).Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
            sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
        }
Esempio n. 9
0
        private static DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
        {
            DataTable dt = new DataTable();

            options = options ?? ReadOptions.DefaultOptions;


            //primera fila de títulos
            bool firstRow = options.TitlesInFirstRow;

            dt.TableName = workSheet.GetNameForDataTable();

            if (!options.TitlesInFirstRow)
            {
                //si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
                foreach (var col in workSheet.ColumnsUsed())
                {
                    dt.Columns.Add(col.ColumnLetter());
                }
            }

            Func <IXLRow, bool> getRows = _ => true;

            if (options.RowStart != 0)
            {
                getRows = (r) => (r.RowNumber() >= options.RowStart);
            }

            foreach (IXLRow row in workSheet.RowsUsed(r => getRows(r)))
            {
                //Usamos la primera fila para crear las columnas con los títulos
                //init with options.TitlesInFirstRow
                if (firstRow)
                {
                    foreach (IXLCell cell in row.CellsUsed())
                    {
                        dt.Columns.Add(cell.Value?.ToString());
                    }
                    firstRow = false;
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;

                    foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }

            return(dt);
        }
Esempio n. 10
0
        private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
        {
            int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
            int ultimaLinhaUsada  = worksheet.LastRowUsed().RowNumber();

            AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
            AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);

            worksheet.ShowGridLines = false;

            worksheet.ColumnsUsed().AdjustToContents();
            worksheet.RowsUsed().AdjustToContents();
        }
Esempio n. 11
0
        private DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
        {
            DataTable dt = new DataTable();

            options = options ?? ReadOptions.DefaultOptions;

            //TODO: Implementar opción con columnas sin títulos

            //primera fila de titulos
            bool firstRow = options.TitlesInFirstRow;

            dt.TableName = workSheet.GetNameForDataTable();

            if (options.TitlesInFirstRow)
            {
                //si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
                foreach (var col in workSheet.ColumnsUsed())
                {
                    dt.Columns.Add(col.ColumnLetter());
                }
            }


            foreach (IXLRow row in workSheet.RowsUsed())
            {
                //Usamos la primera fila para crear las columnas con los títulos
                //init with options.TitlesInFirstRow
                if (firstRow)
                {
                    foreach (IXLCell cell in row.CellsUsed())
                    {
                        dt.Columns.Add(cell.GetContentWithOutSpaces());
                    }
                    firstRow = false;
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;

                    foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }

            return(dt);
        }
Esempio n. 12
0
        public static byte[] GetContentAsBytes(params DataTable[] tables)
        {
            using (var workbook = new XLWorkbook())
            {
                foreach (DataTable dataTable in tables)
                {
                    IXLWorksheet ws = workbook.Worksheets.Add(dataTable);
                    ws.RangeUsed().ColumnsUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    ws.ColumnsUsed().AdjustToContents();
                }

                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    return(stream.ToArray());
                }
            }
        }
Esempio n. 13
0
        public void NoColumnsUsed()
        {
            var          wb    = new XLWorkbook();
            IXLWorksheet ws    = wb.Worksheets.Add("Sheet1");
            Int32        count = 0;

            foreach (IXLColumn row in ws.ColumnsUsed())
            {
                count++;
            }

            foreach (IXLRangeColumn row in ws.Range("A1:C3").ColumnsUsed())
            {
                count++;
            }

            Assert.AreEqual(0, count);
        }
Esempio n. 14
0
        public void SetCompetencies(IXLWorksheet workSheet, int row, Dictionary <string, string> compDic)
        {
            var resultList       = new List <string>();
            var competenciesList = workSheet.Cell(row,
                                                  workSheet.ColumnsUsed().Count()).Value.ToString().Split(';', ' ').ToList();

            //Извлекает компетенции дисциплины
            foreach (var item in competenciesList)
            {
                if (!string.IsNullOrEmpty(item))
                {
                    if (compDic.ContainsKey(item))
                    {
                        resultList.Add($"{item}" + " -" + compDic[item]);//Находит такие же в словаре
                    }
                }
            }
            this.Competencies = "\t" + string.Join(";\n\t", resultList) + ".";//Переводит в строку
        }
        public void DateBottomBorder(IXLWorksheet worksheet, DataTable dateexcel, string BeforeCell, string NextCell)
        {
            string saveCellNumber   = "";
            string saveCellNumber2  = "";
            string BeforeBottomCell = "";
            string NextBottomCell   = "";
            int    range            = 29;
            string Nowtime          = DateTime.Now.ToString("yyyy-MM-dd");

            // 사용중인 컬럼 담기
            var value = worksheet.ColumnsUsed();

            string[]      splitColumns = new string[2];
            List <string> Columns      = new List <string>();
            List <string> ValueTest    = new List <string>();

            // 값 확인용
            for (int i = 0; i < Columns.Count; i++)
            {
                string a = Columns[i];
            }

            foreach (var row in value)
            {
                string test = row.RangeAddress.ToString();
                splitColumns = test.Split(':');
                Columns.Add(splitColumns[0]);
            }


            for (int b = dateexcel.Rows.Count; b > 0; b--)
            {
                string Value  = worksheet.Cell(BeforeCell + (b + 28)).Value.ToString();
                string Value2 = worksheet.Cell(NextCell + (b + 28)).Value.ToString();

                /*          BeforeBottomCell = worksheet.Cell(BeforeCell + (b + 28)).ToString(); // cell 이름
                 *        NextBottomCell  = worksheet.Cell(NextCell + (b + 28)).ToString(); // cell 이름*/

                DateTime Celltime;                                                                                         // 참조될 Cell Type
                DateTime Celltime2;                                                                                        // 참조될 Cell Type

                string BeforeBottomCellValue = worksheet.Cell(BeforeCell + (b + 28)).Style.Border.BottomBorder.ToString(); // 하단 셀 찾기
                string NextBottomCellValue   = worksheet.Cell(NextCell + (b + 28)).Style.Border.BottomBorder.ToString();   // 하단 셀 찾기

                if (NextBottomCellValue.Equals("Medium"))
                { // 다음셀에서 사용할 미디움 발견 즉시 바로 번호 저장
                    saveCellNumber2 = (b + 28).ToString();
                }

                if (BeforeBottomCellValue.Equals("Medium"))
                { // 이전셀에서 미디움 발견 즉시 바로 번호 저장
                    saveCellNumber = (b + 28).ToString();
                }


                // 사용중인 컬럼 값 조회 & 날짜 확인
                for (int i = Columns.Count; i > 0; i--)
                {
                    string columnValue = Columns[i - 1];
                    string compare     = worksheet.Cell(columnValue + (b + range)).Value.ToString();
                    if (DateTime.TryParse(compare, out Celltime))                           // 날짜 타입이면
                    {
                        int celldate = DateTime.Compare(DateTime.Parse(Nowtime), Celltime); // 현재보다 미래면 -1
                        //   int celldateCompare = DateTime.Compare(DateTime.Parse(Nowtime), Celltime2); // 현재보다 미래면 -1

                        // if (celldate > 0) // 과거인 날짜 찾기
                        if (celldate < 0) // 미래인 날짜 찾기
                        {
                            // worksheet.Cell("H" + (b + 29)).Style.Border.RightBorder = XLBorderStyleValues.Medium;
                            // worksheet.Range("H41:I34").Style.Border.RightBorder = XLBorderStyleValues.Medium;
                            ValueTest.Add(columnValue + (b + range));                                                             // 과거인 날짜 컬럼 담기
                            worksheet.Cell(columnValue + (b + range)).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0); // 노란색
                            // 미래 데이터 노란색
                        }
                    }
                }
            }
            ValueTest.Sort();
            // int MinusnSaveCellnumber = Int32.Parse(saveCellNumber2) + 1;
            int MinusnSaveCellnumber = Int32.Parse(saveCellNumber2);

            worksheet.Range(NextCell + MinusnSaveCellnumber.ToString() + ":" + (NextCell + saveCellNumber)).Style.Border.LeftBorder = XLBorderStyleValues.Medium;
            // 반복문을 거꾸로 했기때문에 역순임
        }
 /// <summary>
 /// Apply formatting to the worksheet
 /// </summary>
 /// <param name="worksheet">The worksheet to format</param>
 private void FormatWorksheet(ref IXLWorksheet worksheet)
 {
     worksheet.ColumnsUsed().AdjustToContents();
     worksheet.SheetView.FreezeRows(1);
 }
Esempio n. 17
0
        private void makePurchase(List <m_Purchase> data, IXLWorksheet sheet)
        {
            sheet.Cell(1, 1).Value = "銷售明細總表";
            sheet.Range(1, 1, 1, 14).Merge();
            setFontColorAndBg(sheet, 1, 1, XLColor.White, XLColor.Blue);

            int row_index = 2;

            foreach (var i in data)
            {
                #region 主標題
                sheet.Cell(row_index, 1).Value  = "[訂單編號]";
                sheet.Cell(row_index, 2).Value  = "[購買人]";
                sheet.Cell(row_index, 3).Value  = "[付款方式]";
                sheet.Cell(row_index, 4).Value  = "[付款狀態]";
                sheet.Cell(row_index, 5).Value  = "[出貨狀態]";
                sheet.Cell(row_index, 6).Value  = "[下單日期]";
                sheet.Cell(row_index, 7).Value  = "[運費]";
                sheet.Cell(row_index, 8).Value  = "[手續費]";
                sheet.Cell(row_index, 9).Value  = "[總計金額(含運費)]";
                sheet.Cell(row_index, 10).Value = "[收件人]";
                sheet.Cell(row_index, 11).Value = "[收件人電話]";
                sheet.Cell(row_index, 12).Value = "[收件人手機]";
                sheet.Cell(row_index, 13).Value = "[收件人地址]";
                sheet.Cell(row_index, 14).Value = "[收件備註]";

                setFontColor_Label(sheet, row_index, row_index, 1, 14, XLColor.Blue);
                row_index++;
                #endregion

                sheet.Cell(row_index, 1).Value  = i.purchase_no;
                sheet.Cell(row_index, 2).Value  = i.customer_name;
                sheet.Cell(row_index, 3).Value  = CodeSheet.GetStateVal(i.pay_type, i_CodeName.Value, CodeSheet.IPayTypeData);
                sheet.Cell(row_index, 4).Value  = CodeSheet.GetStateVal(i.pay_state, i_CodeName.Value, CodeSheet.IPayStateData);
                sheet.Cell(row_index, 5).Value  = CodeSheet.GetStateVal(i.ship_state, i_CodeName.Value, CodeSheet.IShipStateData);
                sheet.Cell(row_index, 6).Value  = i.order_date.ToString("yyyy/MM/dd HH:mm");
                sheet.Cell(row_index, 7).Value  = i.ship_fee;
                sheet.Cell(row_index, 8).Value  = i.bank_charges;
                sheet.Cell(row_index, 9).Value  = i.total;
                sheet.Cell(row_index, 10).Value = i.receive_name;
                sheet.Cell(row_index, 11).Value = i.receive_tel;
                sheet.Cell(row_index, 12).Value = i.receive_mobile;
                sheet.Cell(row_index, 13).Value = i.receive_zip + "-" + i.receive_address;
                sheet.Cell(row_index, 14).Value = i.receive_memo;

                row_index++;

                #region 次標題
                sheet.Cell(row_index, 3).Value = "產品購買清單";
                sheet.Range(row_index, 3, row_index, 9).Merge();
                setFontColorAndBg(sheet, row_index, 3, XLColor.White, XLColor.DeepSkyBlue);
                row_index++;

                sheet.Cell(row_index, 3).Value = "[項次]";
                sheet.Cell(row_index, 4).Value = "[產品料號]";
                sheet.Cell(row_index, 5).Value = "[產品名稱]";
                sheet.Cell(row_index, 6).Value = "[產品包裝]";
                sheet.Cell(row_index, 7).Value = "[單價]";
                sheet.Cell(row_index, 8).Value = "[數量]";
                sheet.Cell(row_index, 9).Value = "[小計]";
                setFontColor_Label(sheet, row_index, row_index, 3, 9, XLColor.Blue);
                row_index++;
                #endregion
                int index = 1;
                foreach (var detail in i.Deatil)
                {
                    sheet.Cell(row_index, 3).Value = index;
                    sheet.Cell(row_index, 4).Value = detail.p_d_sn;
                    sheet.Cell(row_index, 5).Value = detail.p_name;
                    sheet.Cell(row_index, 6).Value = detail.p_d_pack_name;
                    sheet.Cell(row_index, 7).Value = detail.price;
                    sheet.Cell(row_index, 8).Value = detail.qty;
                    sheet.Cell(row_index, 9).Value = detail.sub_total;

                    row_index++;
                }

                row_index++;
            }

            sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
        }
Esempio n. 18
0
 /// <summary>
 /// Apply formatting to the worksheet
 /// </summary>
 /// <param name="worksheet"></param>
 private void FormatWorksheet(ref IXLWorksheet worksheet)
 {
     worksheet.ColumnsUsed().AdjustToContents();
     worksheet.Column(2).Width = 30;
     worksheet.Column(2).Style.Alignment.WrapText = true;
 }
    XLWorkbook CreateXlWorkBook(DataSet ImportDs, XLWorkbook wb)
    {
        try
        {
            int Index = 0;

            foreach (DataTable dt in ImportDs.Tables)
            {
                if (dt.Rows.Count <= 0)
                {
                    dt.Columns.Add("No records", typeof(string));
                    dt.Rows.Add("No records");
                }
                if (Index % 2 != 0 || Index == 0)
                {
                    //Add DataTable as Worksheet.

                    IXLWorksheet worksheet = wb.Worksheets.Add(dt);
                    worksheet.Tables.FirstOrDefault().ShowAutoFilter = false;

                    /////      Add Header        ///
                    worksheet.Row(1).InsertRowsAbove(1);
                    worksheet.Row(2).InsertRowsAbove(1);
                    worksheet.Row(3).InsertRowsAbove(1);

                    int UsedCol = worksheet.ColumnsUsed().Count();
                    // worksheet.Row(1).Cell(UsedCol).Hyperlink.Cell.Value = "Help";
                    // worksheet.Row(1).Cell(UsedCol).Hyperlink.InternalAddress = "www.gstsaathiaccounts.in";
                    // worksheet.Row(1).Cell(UsedCol).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);


                    // Put a Blue Color in a 2 Rows Of Cells
                    foreach (var c in Enumerable.Range(1, UsedCol))
                    {
                        //worksheet.Cell(2, c).Style.Border.
                        worksheet.Cell(2, c).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                        worksheet.Cell(2, c).Style.Font.FontColor       = XLColor.White;
                        //   worksheet.Cell(2, c).Style.Border.OutsideBorderColor = XLColor.Black;
                    }

                    // Put a Orange Color in a 4 Rows Of Cells Of Headers of Data
                    foreach (var c in Enumerable.Range(1, UsedCol))
                    {
                        worksheet.Cell(4, c).Style.Fill.BackgroundColor = XLColor.FromArgb(250, 191, 143);
                        worksheet.Cell(4, c).Style.Font.FontColor       = XLColor.Black;
                        // worksheet.Cell(4, c).Style.Border.OutsideBorderColor = XLColor.Black;
                    }

                    //  Excel Name Row 1 Cell 1;
                    //worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                    //worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.White;
                    worksheet.Cells("A1").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                    worksheet.Cells("A1").Style.Font.FontColor       = XLColor.White;

                    #region b2b
                    if (worksheet.Name == "b2b")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2B(4)";
                        worksheet.Cell("A2").Value     = "No. of Recipients";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "Total Invoice Value";
                        worksheet.Cell("J2").Value     = "Total Taxable Value";
                        worksheet.Cell("K2").Value     = "Total Cess";

                        //if (dt.Rows.Count > 0)
                        //{
                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[2].Rows[0]["TotalParty"].ToString();
                        worksheet.Cell("B3").Value = ImportDs.Tables[2].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[2].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("J3").Value = ImportDs.Tables[2].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[2].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                        //}
                    }
                    #endregion

                    #region b2cl
                    if (worksheet.Name == "b2cl")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2CL(5)";
                        worksheet.Cell("A2").Value     = "No. of Invoices";
                        worksheet.Cell("C2").Value     = "Total Invoice Value";
                        worksheet.Cell("F2").Value     = "Total Taxable Value";
                        worksheet.Cell("G2").Value     = "Total Cess";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[4].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("C3").Value = ImportDs.Tables[4].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[4].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("G3").Value = ImportDs.Tables[4].Rows[0]["Cess"].ToString();
                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region b2cs
                    if (worksheet.Name == "b2cs")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2CS(7)";

                        worksheet.Cell("D2").Value = "Total Taxable Value";
                        worksheet.Cell("E2").Value = "Total Cess";


                        //DataView view = new DataView(dt);

                        worksheet.Cell("D3").Value = ImportDs.Tables[6].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[6].Rows[0]["Cess"].ToString();
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region cdnr
                    if (worksheet.Name == "cdnr")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For CDNR(9B)";
                        worksheet.Cell("A2").Value     = "No. of Recipients";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "No. of Notes/Vouchers";
                        worksheet.Cell("I2").Value     = "Total Note/Refund Voucher Value";
                        worksheet.Cell("K2").Value     = "Total Taxable Value";
                        worksheet.Cell("L2").Value     = "Total Cess";



                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[8].Rows[0]["TotalReciepent"].ToString();
                        worksheet.Cell("B3").Value = ImportDs.Tables[8].Rows[0]["AmdTotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[8].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[8].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[8].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("L3").Value = ImportDs.Tables[8].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
                        //view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
                        //view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
                        //dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region cdnur
                    if (worksheet.Name == "cdnur")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For CDNUR(9B)";
                        worksheet.Cell("B2").Value     = "No. of Notes/Vouchers";
                        worksheet.Cell("E2").Value     = "No. of Invoices";
                        worksheet.Cell("I2").Value     = "Total Note Value";
                        worksheet.Cell("K2").Value     = "Total Taxable Value";
                        worksheet.Cell("L2").Value     = "Total Cess";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[10].Rows[0]["AmdTotalInvoice"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[10].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[10].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[10].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("L3").Value = ImportDs.Tables[10].Rows[0]["Cess"].ToString();

                        // view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
                        //view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
                        //dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region exp
                    if (worksheet.Name == "exp")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For EXP(6)";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "Total Note Value";
                        worksheet.Cell("F2").Value     = "No. of Shipping Bill";
                        worksheet.Cell("I2").Value     = "Total Taxable Value";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[12].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[12].Rows[0]["InvoiceValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[12].Rows[0]["ShippedInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[12].Rows[0]["Taxablevalue"].ToString();

                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //view.ToTable(true, "Shipping Bill Number").Rows.Count;
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                    }
                    #endregion

                    #region at
                    if (worksheet.Name == "at")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
                        worksheet.Cell("C2").Value     = "Total Advance Received";
                        worksheet.Cell("D2").Value     = "Total Cess";


                        worksheet.Cell("C3").Value = ImportDs.Tables[14].Rows[0]["GrossReceived"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[14].Rows[0]["CessAmt"].ToString();
                        //dt.Compute("SUM([Gross Advance Received])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region atadj
                    if (worksheet.Name == "atadj")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
                        worksheet.Cell("C2").Value     = "Total Advance Adjusted";
                        worksheet.Cell("D2").Value     = "Total Cess";


                        worksheet.Cell("C3").Value = ImportDs.Tables[16].Rows[0]["GrossReceived"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[16].Rows[0]["CessAmt"].ToString();
                    }
                    #endregion

                    #region exemp
                    if (worksheet.Name == "exemp")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Nil rated, exempted and non GST outward supplies (8)";

                        worksheet.Cell("B2").Value = "Total Nil Rated Supplies";
                        worksheet.Cell("C2").Value = "Total Exempted Supplies";
                        worksheet.Cell("D2").Value = "Total Non-GST Supplies";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[18].Rows[0]["NillRated"].ToString();
                        worksheet.Cell("C3").Value = ImportDs.Tables[18].Rows[0]["ExmTotal"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[18].Rows[0]["NonGstTotal"].ToString();

                        //dt.Compute("SUM([Nil Rated Supplies])", string.Empty);
                        //dt.Compute("SUM([Exempted (other than nil rated/non GST supply )])", string.Empty);
                        //dt.Compute("SUM([Non-GST supplies])", string.Empty);
                    }
                    #endregion

                    #region hsn
                    if (worksheet.Name == "hsn")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For HSN(12)";
                        worksheet.Cell("A2").Value     = "No. of HSN";
                        worksheet.Cell("E2").Value     = "Total Value";
                        worksheet.Cell("F2").Value     = "Total Taxable Value";
                        worksheet.Cell("G2").Value     = "Total Integrated Tax";
                        worksheet.Cell("H2").Value     = "Total Central Tax";
                        worksheet.Cell("I2").Value     = "Total State/UT Tax";
                        worksheet.Cell("J2").Value     = "Total Cess";

                        //if (dt.Rows.Count > 0)
                        //{
                        // DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[20].Rows[0]["HSN"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[20].Rows[0]["TotalValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[20].Rows[0]["TotalTaxValue"].ToString();
                        worksheet.Cell("G3").Value = ImportDs.Tables[20].Rows[0]["IGSTAmt"].ToString();
                        worksheet.Cell("H3").Value = ImportDs.Tables[20].Rows[0]["CGSTAmt"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[20].Rows[0]["SGSTAmt"].ToString();
                        worksheet.Cell("J3").Value = ImportDs.Tables[20].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "HSN").Rows.Count;
                        //dt.Compute("SUM([Total Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Integrated Tax Amount])", string.Empty);
                        //dt.Compute("SUM([Central Tax Amount])", string.Empty);
                        //dt.Compute("SUM([State/UT Tax Amount])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);

                        worksheet.Column("D").CellsUsed().Style.NumberFormat.Format = "0.00";
                        worksheet.Row(3).Cell("E").Style.NumberFormat.Format        = "0.00";
                        worksheet.Row(3).Cell("F").Style.NumberFormat.Format        = "0.00";

                        //var validE3 = worksheet.Row(3).Cell("E").DataValidation;
                        //validE3.Decimal.GetType();
                        //validE3.ErrorStyle = XLErrorStyle.Warning;
                        //validE3.ErrorTitle = "Number out of range";
                        //validE3.ErrorMessage = "This cell only allows the number 2.";

                        //worksheet.Cell("E3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
                        //worksheet.Cell("F3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
                        //worksheet.Range("D4", "D" + worksheet.RowsUsed().Count()).Style.NumberFormat.Format = "0.00";
                        //worksheet.CellsUsed("A").Style.NumberFormat.Format = "0.00";
                        //worksheet.Columns(4,6).CellsUsed().Style.NumberFormat.Format = "0.00";
                        // }
                    }
                    #endregion

                    #region docs
                    if (worksheet.Name == "docs")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary of documents issued during the tax period (13)";
                        worksheet.Cell("D2").Value     = "Total Number";
                        worksheet.Cell("E2").Value     = "Total Cancelled";


                        // DataView view = new DataView(dt);
                        worksheet.Cell("D3").Value = ImportDs.Tables[22].Rows[0]["TotalNumber"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[22].Rows[0]["Cancelled"].ToString();

                        //dt.Compute("SUM([Total Number])", string.Empty);
                        //dt.Compute("SUM([Cancelled])", string.Empty);
                    }
                    #endregion
                }
                Index++;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
            throw ex;
        }
        return(wb);
    }
Esempio n. 20
0
        public ExportarKardexResponseDTO ExportarKardex(KardexPergaminoIngresoConsultaRequest request)
        {
            ExportarKardexResponseDTO respose = new ExportarKardexResponseDTO();

            try
            {
                using (var workbook = new XLWorkbook())
                {
                    IXLWorksheet ws = workbook.Worksheets.Add("Kardex");
                    ws.Style.Fill.BackgroundColor = XLColor.White;
                    ws.Range("A1:BA90").Style.Fill.BackgroundColor = XLColor.White;
                    ws.Column("A").Width = 1.71;
                    ws.Column("B").Width = 11.29;

                    //ws.Cell(2, 2).Value = "Kardex:";
                    //ws.Cell(2, 2).Style.Font.Bold = true;
                    //ws.Cell(3, 2).Value = "Producto:";
                    //ws.Cell(3, 2).Style.Font.Bold = true;
                    //ws.Cell(4, 2).Value = "Sub Producto:";
                    //ws.Cell(4, 2).Style.Font.Bold = true;
                    ws.Cell(3, 2).Value           = "Fecha Inicio:";
                    ws.Cell(3, 2).Style.Font.Bold = true;
                    ws.Cell(4, 2).Value           = "Fecha Fin:";
                    ws.Cell(4, 2).Style.Font.Bold = true;

                    ws.Cell(1, 7).Value           = "KARDEX PERGAMINO";
                    ws.Cell(1, 7).Style.Font.Bold = true;
                    //PRIMERA TABLA

                    /*
                     * ws.Cell(2, 8).Value = "Sacos";
                     * ws.Cell(2, 8).Style.Font.Bold = true;
                     * ws.Cell(2, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(2, 8).Style.Alignment.WrapText = true;
                     * ws.Cell(2, 9).Value = "Kgs Netos a pagar";
                     * ws.Cell(2, 9).Style.Font.Bold = true;
                     * ws.Cell(2, 9).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(2, 10).Value = "Moneda";
                     * ws.Cell(2, 10).Style.Font.Bold = true;
                     * ws.Cell(2, 10).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(2, 11).Value = "Importe";
                     * ws.Cell(2, 11).Style.Font.Bold = true;
                     * ws.Cell(2, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(3, 7).Value = "Inventario Actual:";
                     * ws.Cell(3, 7).Style.Font.Bold = true;
                     * ws.Cell(3, 7).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(3, 7).Style.Fill.BackgroundColor = XLColor.LightGray;
                     * ws.Range("H2:K2").Style.Fill.BackgroundColor = XLColor.LightGray;
                     * ws.Range("H2:K2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                     * ws.Range("H2:K2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                     */

                    ws.Cell(8, 2).Value                      = "INGRESOS";
                    ws.Cell(8, 2).Style.Font.Bold            = true;
                    ws.Cell(8, 2).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                    ws.Cell(8, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    IXLRange rangeIngresos = ws.Range(ws.Cell(8, 2), ws.Cell(8, 19));
                    rangeIngresos.Merge();
                    rangeIngresos.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    rangeIngresos.Style.Fill.BackgroundColor = XLColor.FromHtml("#479d9c");//XLColor.YellowProcess;

                    ws.Cell(8, 20).Value                      = "SALIDAS";
                    ws.Cell(8, 20).Style.Font.Bold            = true;
                    ws.Cell(8, 20).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                    ws.Cell(8, 20).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    IXLRange rangeSalidas = ws.Range(ws.Cell(8, 20), ws.Cell(8, 31));
                    rangeSalidas.Merge();
                    rangeSalidas.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    rangeSalidas.Style.Fill.BackgroundColor = XLColor.FromHtml("#479d9c");

                    //INGRESOS
                    ws.Cell(9, 2).Value  = "Fecha Registro";
                    ws.Cell(9, 3).Value  = "Nro. Guia de Recepcion";
                    ws.Cell(9, 4).Value  = "Observaciones";
                    ws.Cell(9, 5).Value  = "Nombre/Razon Social";
                    ws.Cell(9, 6).Value  = "Nro. Documento";
                    ws.Cell(9, 7).Value  = "Zona";
                    ws.Cell(9, 8).Value  = "Producto";
                    ws.Cell(9, 9).Value  = "Cantidad";
                    ws.Cell(9, 10).Value = "Kilos Brutos";
                    ws.Cell(9, 11).Value = "Tara";
                    ws.Cell(9, 12).Value = "Kilos Netos a Descontar";
                    ws.Cell(9, 13).Value = "Kilos Netos a Pagar";
                    ws.Cell(9, 14).Value = "Precio del Día";
                    ws.Cell(9, 15).Value = "Importe";
                    ws.Cell(9, 16).Value = "% Humedad";
                    ws.Cell(9, 17).Value = "% Rendimiento (Exportable)";
                    ws.Cell(9, 18).Value = "Calculo (kilos Netos a Pagar x % Humedad)";
                    ws.Cell(9, 19).Value = "Calculo (kilos Netos a Pagar x % Rendimiento (Exportable))";


                    //SALIDAS
                    ws.Cell(9, 20).Value = "Nro. Guia de Remisión Electronica";
                    ws.Cell(9, 21).Value = "Fecha Guia de Remisión Electronica";
                    ws.Cell(9, 22).Value = "Calculo (Kilos Netos * % Humedad)";
                    ws.Cell(9, 23).Value = "Calculo (Kilos Netos * % Rendimiento)";
                    ws.Cell(9, 24).Value = "Cantidad";
                    ws.Cell(9, 25).Value = "Kilos Brutos";
                    ws.Cell(9, 26).Value = "Tara";
                    ws.Cell(9, 27).Value = "Kilos Netos a Descontar";
                    ws.Cell(9, 28).Value = "Kilos Netos";
                    ws.Cell(9, 29).Value = "% Humedad";
                    ws.Cell(9, 30).Value = "% Rendimiento";
                    ws.Cell(9, 31).Value = "Saldo";

                    for (int i = 2; i <= 19; i++)
                    {
                        ws.Cell(9, i).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#479d9c"));
                        ws.Cell(9, i).Style.Font.Bold            = true;
                        ws.Cell(9, i).Style.Alignment.WrapText   = true;
                        ws.Cell(9, i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        ws.Cell(9, i).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                        ws.Cell(9, i).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    }

                    for (int i = 20; i <= 31; i++)
                    {
                        ws.Cell(9, i).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#479d9c"));
                        ws.Cell(9, i).Style.Font.Bold            = true;
                        ws.Cell(9, i).Style.Alignment.WrapText   = true;
                        ws.Cell(9, i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        ws.Cell(9, i).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                        ws.Cell(9, i).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    }

                    //INICIO: DATA HARDCODE

                    //ws.Cell(2, 3).Value = "Almacén 01";
                    // ws.Cell(3, 3).Value = "Café Pergamino";
                    // ws.Cell(4, 3).Value = "Seco";
                    ws.Cell(3, 3).Value = request.FechaInicio.ToShortDateString();
                    ws.Cell(4, 3).Value = request.FechaFin.ToShortDateString();
                    ws.Range("D2:D6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    ws.Range("D2:D6").Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;

                    /*
                     * ws.Cell(3, 8).Value = 24;
                     * ws.Cell(3, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(3, 9).Value = 1286.20;
                     * ws.Cell(3, 9).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(3, 10).Value = "Soles";
                     * ws.Cell(3, 10).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Cell(3, 11).Value = 9727.03;
                     * ws.Cell(3, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     *
                     *
                     * ws.Cell("G10").Value = "SALDO ANTERIOR";
                     * ws.Cell("G10").Style.Font.Bold = true;
                     * ws.Cell("G10").Style.Font.FontColor = XLColor.Red;
                     *
                     * ws.Cell("R10").Value = "Saco";
                     * ws.Cell("S10").Value = 20;
                     * ws.Cell("V10").Value = 1096;
                     * ws.Cell("X10").Value = 48.5;
                     * ws.Cell("Y10").Value = 1047.5;
                     * ws.Cell("Z10").Value = "Soles";
                     * ws.Cell("AB10").Value = 7891.20;
                     *
                     * ws.Range("B10:AH10").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Range("AI10:AZ10").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     */
                    /*
                     * KardexPergaminoIngresoConsultaRequest requestIngreso = new KardexPergaminoIngresoConsultaRequest();
                     * requestIngreso.EmpresaId = 1;
                     * requestIngreso.FechaInicio = Convert.ToDateTime("2018-07-19T00:00:00");
                     * requestIngreso.FechaFin = Convert.ToDateTime("2024-07-19T00:00:00");
                     */
                    var list = _IKardexProcesoRepository.KardexPergaminoIngresoConsulta(request);
                    List <KardexPergaminoIngresoConsultaResponse> listaIngreso = list.ToList();

                    decimal sumaKilosBrutos = 0;
                    decimal sumaKilosNetos  = 0;
                    decimal sumaImporte     = 0;
                    decimal sumaRemdimiento = 0;
                    for (int i = 1; i <= listaIngreso.Count; i++)
                    {
                        ws.Cell(9 + i, 2).Value  = listaIngreso[i - 1].FechaRegistro.ToShortDateString();
                        ws.Cell(9 + i, 3).Value  = listaIngreso[i - 1].Numero;
                        ws.Cell(9 + i, 4).Value  = listaIngreso[i - 1].ObservacionPesado;
                        ws.Cell(9 + i, 5).Value  = listaIngreso[i - 1].NombreRazonSocial;
                        ws.Cell(9 + i, 6).Value  = listaIngreso[i - 1].NumeroDocumento;
                        ws.Cell(9 + i, 7).Value  = listaIngreso[i - 1].Zona;
                        ws.Cell(9 + i, 8).Value  = listaIngreso[i - 1].Producto;
                        ws.Cell(9 + i, 9).Value  = listaIngreso[i - 1].CantidadPesado;
                        ws.Cell(9 + i, 10).Value = listaIngreso[i - 1].KilosBrutosPesado;
                        ws.Cell(9 + i, 11).Value = listaIngreso[i - 1].TaraPesado;
                        ws.Cell(9 + i, 12).Value = listaIngreso[i - 1].KilosNetosDescontar;
                        ws.Cell(9 + i, 13).Value = listaIngreso[i - 1].KilosNetosPagar;
                        ws.Cell(9 + i, 14).Value = listaIngreso[i - 1].PrecioPagado;
                        ws.Cell(9 + i, 15).Value = listaIngreso[i - 1].Importe;
                        ws.Cell(9 + i, 16).Value = listaIngreso[i - 1].HumedadPorcentajeAnalisisFisico;
                        ws.Cell(9 + i, 17).Value = listaIngreso[i - 1].ExportablePorcentajeAnalisisFisico;
                        ws.Cell(9 + i, 18).Value = listaIngreso[i - 1].CalculoKilosNetosPagarPorHumedad;
                        ws.Cell(9 + i, 19).Value = listaIngreso[i - 1].CalculoKilosNetosPagarPorRendimiento;
                        sumaKilosBrutos          = sumaKilosBrutos + listaIngreso[i - 1].KilosBrutosPesado;
                        sumaKilosNetos           = sumaKilosNetos + listaIngreso[i - 1].KilosNetosPagar;
                        sumaImporte     = sumaImporte + listaIngreso[i - 1].Importe;
                        sumaRemdimiento = sumaRemdimiento + listaIngreso[i - 1].ExportablePorcentajeAnalisisFisico;
                    }
                    ws.Cell(9 + listaIngreso.Count + 1, 10).Value           = sumaKilosBrutos;
                    ws.Cell(9 + listaIngreso.Count + 1, 10).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 13).Value           = sumaKilosNetos;
                    ws.Cell(9 + listaIngreso.Count + 1, 13).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 15).Value           = sumaImporte;
                    ws.Cell(9 + listaIngreso.Count + 1, 15).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 17).Value           = listaIngreso.Count > 0 ? sumaRemdimiento / listaIngreso.Count : 0;
                    ws.Cell(9 + listaIngreso.Count + 1, 17).Style.Font.Bold = true;

                    KardexPergaminoSalidaConsultaRequest requestSalida = new KardexPergaminoSalidaConsultaRequest();
                    requestSalida.EmpresaId   = request.EmpresaId;
                    requestSalida.FechaInicio = request.FechaInicio;
                    requestSalida.FechaFin    = request.FechaFin;
                    var listSalida = _IKardexProcesoRepository.KardexPergaminoSalidadConsulta(requestSalida);
                    List <KardexPergaminoSalidaConsultaResponse> listaSalida = listSalida.ToList();

                    decimal sumaKilosBrutosSalida   = 0;
                    decimal sumaTaraSalida          = 0;
                    decimal sumaKilosNetosSalida    = 0;
                    decimal sumaKilosNetosDescontar = 0;



                    for (int i = 1; i <= listaSalida.Count; i++)
                    {
                        ws.Cell(9 + i, 20).Value = listaSalida[i - 1].NumeroGuiaRemision;
                        ws.Cell(9 + i, 21).Value = listaSalida[i - 1].FechaGuiaRemision.ToShortDateString();
                        ws.Cell(9 + i, 22).Value = listaSalida[i - 1].CalculoKilosNetosPagarPorHumedad;
                        ws.Cell(9 + i, 23).Value = listaSalida[i - 1].CalculoKilosNetosPagarPorRendimiento;
                        ws.Cell(9 + i, 24).Value = listaSalida[i - 1].Cantidad;
                        ws.Cell(9 + i, 25).Value = listaSalida[i - 1].TotalKilosBrutosPesado;
                        ws.Cell(9 + i, 26).Value = listaSalida[i - 1].Tara;
                        ws.Cell(9 + i, 27).Value = listaSalida[i - 1].TotalKilosNetosDescontar;
                        ws.Cell(9 + i, 28).Value = listaSalida[i - 1].TotalKilosNetosPesado;
                        ws.Cell(9 + i, 29).Value = listaSalida[i - 1].HumedadPorcentajeAnalisisFisico;
                        ws.Cell(9 + i, 30).Value = listaSalida[i - 1].RendimientoPorcentaje;
                        sumaKilosBrutosSalida    = sumaKilosBrutosSalida + listaSalida[i - 1].TotalKilosBrutosPesado;
                        sumaTaraSalida           = sumaTaraSalida + listaSalida[i - 1].Tara;
                        sumaKilosNetosSalida     = sumaKilosNetosSalida + listaSalida[i - 1].TotalKilosNetosPesado;
                        sumaKilosNetosDescontar  = sumaKilosNetosDescontar + listaSalida[i - 1].TotalKilosNetosDescontar;
                    }

                    ws.Cell(9 + listaIngreso.Count + 1, 25).Value           = sumaKilosBrutosSalida;
                    ws.Cell(9 + listaIngreso.Count + 1, 25).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 26).Value           = sumaTaraSalida;
                    ws.Cell(9 + listaIngreso.Count + 1, 26).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 27).Value           = sumaKilosNetosDescontar;
                    ws.Cell(9 + listaIngreso.Count + 1, 27).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 28).Value           = sumaKilosNetosSalida;
                    ws.Cell(9 + listaIngreso.Count + 1, 28).Style.Font.Bold = true;

                    ws.Cell(9 + listaIngreso.Count + 1, 31).Value           = sumaKilosNetos - sumaKilosNetosSalida;
                    ws.Cell(9 + listaIngreso.Count + 1, 31).Style.Font.Bold = true;

                    int g = 0;

                    for (int i = 0; i <= listaIngreso.Count - 1; i++)
                    {
                        g = 10 + i;
                        for (int y = 2; y <= 31; y++)
                        {
                            ws.Cell(g, y).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        }
                    }

                    /*
                     * ws.Cell("R15").Value = 70;
                     * ws.Cell("V15").Value = 4249.5;
                     * ws.Cell("Y15").Value = 4111.32;
                     * ws.Cell("Z15").Value = "Soles";
                     * ws.Cell("AB15").Value = 31099.83;
                     * ws.Cell("AO15").Value = 46;
                     * ws.Cell("AR15").Value = 2963.30;
                     * ws.Cell("AU15").Value = 2901.04;
                     * ws.Cell("AX15").Value = 21372.80;
                     *
                     * ws.Range("B15:AH15").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Range("B15:AH15").Style.Fill.BackgroundColor = XLColor.LightGray;
                     * ws.Range("AI15:AZ15").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                     * ws.Range("AI15:AZ15").Style.Fill.BackgroundColor = XLColor.LightGray;
                     */
                    //FIN: DATA HARDCODE

                    ws.ColumnsUsed().AdjustToContents();
                    ws.RowsUsed().AdjustToContents();

                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        respose.content = stream.ToArray();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return(respose);
        }
Esempio n. 21
0
        private int GetIndexExistRow(IXLWorksheet worksheet, int workingRow, int workingCol, int workingIndex, string workingValue, Dictionary <string, List <string> > workingDictionary)
        {
            int rowCount = 0;
            int colCount = 0;

            var rowUsed = worksheet.RowsUsed();
            var colUsed = worksheet.ColumnsUsed();

            foreach (var row in rowUsed)
            {
                if (row.RowNumber() > 1)
                {
                    string currentValue = common.GetValue(worksheet, row.RowNumber(), workingCol);

                    if (currentValue == workingValue)
                    {
                        // get row has exist data
                        int rowExist = row.RowNumber();

                        // loop all col check data
                        int colLoopCount      = 0;
                        int colExistLoopCount = 0;

                        foreach (var col in colUsed)
                        {
                            // check exist data clo bigger col working
                            if (col.ColumnNumber() > workingCol)
                            {
                                // get header name
                                string nameHeader = common.GetValue(worksheet, 1, col.ColumnNumber());

                                // get value dictionary by key (header name)
                                List <string> list = workingDictionary[nameHeader];

                                string valueChecking = list[workingIndex];

                                if (valueChecking != "")
                                {
                                    // get current value
                                    currentValue = common.GetValue(worksheet, rowExist, col.ColumnNumber());

                                    if (valueChecking == currentValue)
                                    {
                                        colExistLoopCount++;
                                    }
                                    colLoopCount++;
                                }
                            }
                            colCount++;
                        }

                        if (colExistLoopCount == colLoopCount)
                        {
                            return(workingIndex);
                        }
                    }
                }
                rowCount++;
            }

            return(-1);
        }
Esempio n. 22
0
        public async Task <ActionResult> OrderPembelianExcel(int id)
        {
            string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            try
            {
                var pembelian = await _pembelianService.GetOrder(id);

                string fileName = $"Order Pembelian No:{pembelian.Nomor}.xlsx";

                using var workbook = new XLWorkbook();
                IXLWorksheet worksheet = workbook.Worksheets.Add("Order");
                worksheet.Cell(1, 1).Value = "ORDER PEMBELIAN";
                worksheet.Cell(2, 1).Value = $"Kepada : {pembelian.Supplier.Nama}";

                var range = worksheet.Range("A1:G1");
                range.Merge().Style.Font.SetBold().Font.FontSize = 16;

                var range2 = worksheet.Range("A2:G2");
                range2.Merge().Style.Font.SetBold().Font.FontSize = 14;

                var start = 4;
                worksheet.Cell(start, 1).Value = "Nomor";
                worksheet.Cell(start, 2).Value = "Article";
                worksheet.Cell(start, 3).Value = "Nama";
                worksheet.Cell(start, 4).Value = "Jumlah";
                worksheet.Cell(start, 5).Value = "Harga";
                worksheet.Cell(start, 6).Value = $"Discount";
                worksheet.Cell(start, 7).Value = "Total";

                var items = pembelian.Items.ToList();
                int nomor = 1;
                for (int index = 1; index <= items.Count; index++)
                {
                    var total = items[index - 1].Total;
                    worksheet.Cell(index + start, 1).Value = nomor;
                    worksheet.Cell(index + start, 2).Value = items[index - 1].Product.CodeArticle;
                    worksheet.Cell(index + start, 3).Value = items[index - 1].Product.Name + " | " + items[index - 1].Product.Size;
                    worksheet.Cell(index + start, 4).Value = items[index - 1].Quntity;
                    worksheet.Cell(index + start, 5).Value = items[index - 1].Price;


                    worksheet.Cell(index + start, 6).Value = total * 10 / 100;
                    worksheet.Cell(index + start, 7).Value = total - (total * 10 / 100);

                    worksheet.Cell(index + start, 5).Style.NumberFormat.Format = "0,000.00";
                    worksheet.Cell(index + start, 6).Style.NumberFormat.Format = "0,000.00";
                    worksheet.Cell(index + start, 7).Style.NumberFormat.Format = "0,000.00";
                    nomor++;
                }

                worksheet.Range($"A{start}:G{items.Count + start}").Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
                worksheet.Range($"A{start}:G{items.Count + start}").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

                foreach (var item in worksheet.ColumnsUsed())
                {
                    item.AdjustToContents();
                }


                worksheet.Cell(items.Count + 8, 1).Value  = "Hormat Kami";
                worksheet.Cell(items.Count + 12, 1).Value = "Elish";
                var rangeAsign = worksheet.Range($"A{items.Count + 8}:B{items.Count + 8}");
                rangeAsign.Merge().Style.Font.SetBold().Font.FontSize = 12;

                using var stream = new MemoryStream();
                workbook.SaveAs(stream);
                var content = stream.ToArray();
                return(File(content, contentType, fileName));
            }
            catch (Exception ex)
            {
                return(BadRequest(ex.Message));
            }
        }
Esempio n. 23
0
        // 创建一个 Sheet
        IXLWorksheet CreateSheet(XLWorkbook doc,
                                 string sheet_name,
                                 List <TransferGroup> groups)
        {
            IXLWorksheet sheet = doc.Worksheets.Add(Cut(GetSheetName(sheet_name), 31));

            // 每个列的最大字符数
            // List<int> column_max_chars = new List<int>();

            // 准备书目列标题
            var biblio_column_option = new TransferColumnOption(Program.MainForm.UserDir);

            biblio_column_option.LoadData(Program.MainForm.AppInfo,
                                          ColumnDefPath);

            List <Order.ColumnProperty> biblio_title_list = Order.DistributeExcelFile.BuildList(biblio_column_option);

            List <string> headers = new List <string>();

            biblio_title_list.ForEach(o => headers.Add(o.Caption));

            int nRowIndex = 1;
            int nColIndex = 1;

            // 输出表格标题
            {
                var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
                range.Merge();
                range.SetValue(sheet_name);
                range.Style.Alignment.WrapText   = true;
                range.Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                range.Style.Font.Bold            = true;
                nRowIndex++;
            }

            int lines = 0;

            groups.ForEach(o => lines += o.Items.Count);
            {
                var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
                range.Merge();
                range.SetValue($"行数: {lines}, 打印日期: {DateTime.Now.ToString("yyyy-M-d")}");
                range.Style.Alignment.WrapText   = true;
                range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                nRowIndex++;
            }

            nRowIndex++;
            foreach (string header in headers)
            {
                IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(header, '*'));
                cell.Style.Alignment.WrapText = true;
                cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                cell.Style.Font.Bold          = true;
                //cell.Style.Font.FontName = strFontName;
                //cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
                nColIndex++;
            }
            {
                // 设置边框
                var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count);
                range.Style.Border.SetBottomBorderColor(XLColor.Black);
                range.Style.Border.SetBottomBorder(XLBorderStyleValues.Medium);
            }

            nRowIndex++;

            // 用于发现重复的册
            // recpath --> (此路径的第一个) TransferItem
            Hashtable recpath_table   = new Hashtable();
            int       dup_count       = 0; // 记录路径发生重复的行数
            int       notchange_count = 0; // 馆藏地没有发生改变的行数

            int nNo = 1;

            foreach (var group in groups)
            {
                foreach (var item in group.Items)
                {
                    bool dup = false;
                    if (recpath_table.ContainsKey(item.RecPath))
                    {
                        dup = true;
                        dup_count++;
                    }
                    else
                    {
                        recpath_table[item.RecPath] = item;
                    }

                    bool onlyWriteLog = StringUtil.IsInList("onlyWriteLog", item.Style);

                    nColIndex = 1;
                    // WriteCell(nNo.ToString());
                    // WriteCell(item.Barcode);

                    // 获得册记录
                    XmlDocument itemdom = new XmlDocument();
                    itemdom.LoadXml(item.NewXml);
                    string strParentID = DomUtil.GetElementText(itemdom.DocumentElement, "parent");

                    // 输出书目列
                    WriteBiblioColumns(
                        item.RecPath,
                        strParentID,
                        biblio_title_list,
                        sheet,
                        nColIndex - 1,
                        nRowIndex - 1);

                    OutputTransferColumns(
                        nNo,
                        item,
                        sheet,
                        nColIndex - 1,
                        Order.ColumnProperty.GetTypeList(biblio_title_list, false),
                        nRowIndex - 1);

                    /*
                     * nColIndex += biblio_title_list.Count;
                     *
                     * WriteCell(item.SourceLocation);
                     * WriteCell(item.TargetLocation);
                     * WriteCell(item.BatchNo);
                     * WriteCell(item.OperTime.ToString());
                     * WriteCell(item.Operator);
                     */

                    // 设置边框
                    var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count);
                    range.Style.Border.SetBottomBorderColor(XLColor.Black);
                    range.Style.Border.SetBottomBorder(XLBorderStyleValues.Hair);

                    if (dup)
                    {
                        range.Style.Fill.BackgroundColor = XLColor.Yellow;
                    }

                    if (onlyWriteLog)
                    {
                        notchange_count++;
                        // 寻找 SourceLocation 和 TargetLocation 列
                        {
                            int index = biblio_title_list.FindIndex(o => o.Type == "log_sourceLocation");
                            if (index != -1)
                            {
                                sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue;
                            }
                        }

                        {
                            int index = biblio_title_list.FindIndex(o => o.Type == "log_targetLocation");
                            if (index != -1)
                            {
                                sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue;
                            }
                        }
                    }

                    nRowIndex++;
                    nNo++;
                }
            }

            // 警告行
            if (dup_count > 0)
            {
                nRowIndex++;
                var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
                range.Merge();

                range.Style.Fill.BackgroundColor = XLColor.DarkGray;
                range.Style.Font.FontColor       = XLColor.White;
                range.Style.Font.Bold            = true;

                range.SetValue($"警告:表中有 {dup_count} 个重复的册记录行。这些行已显示为黄色背景色");
                range.Style.Alignment.WrapText   = true;
                range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                nRowIndex++;
            }

            // 警告行
            if (notchange_count > 0)
            {
                var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
                range.Merge();

                range.Style.Fill.BackgroundColor = XLColor.DarkGray;
                range.Style.Font.FontColor       = XLColor.White;

                range.SetValue($"提醒:表中有 {notchange_count} 个册记录行在移交操作中馆藏地并没有发生变化。这些行的源、目标馆藏地列已显示为蓝色背景色");
                range.Style.Alignment.WrapText   = true;
                range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                nRowIndex++;
            }

            // TODO: 设置 header 和 footer
            // https://stackoverflow.com/questions/34104107/closedxml-change-existing-footer-header

            double char_width = DigitalPlatform.dp2.Statis.ClosedXmlUtil.GetAverageCharPixelWidth(Program.MainForm);

            // 字符数太多的列不要做 width auto adjust
            const int MAX_CHARS = 30;   // 60
            int       i         = 0;

            foreach (IXLColumn column in sheet.ColumnsUsed())
            {
                int nChars = GetMaxChars(column);

                column.Width = Math.Min(nChars, MAX_CHARS);

#if NO
                if (nChars < MAX_CHARS)
                {
                    // column.AdjustToContents();
                    column.Width = nChars;
                }
                else
                {
                    int nColumnWidth = 100;

                    /*
                     * // 2020/1/6 增加保护判断
                     * if (i >= 0 && i < list.Columns.Count)
                     *  nColumnWidth = list.Columns[i].Width;
                     */
                    column.Width = (double)nColumnWidth / char_width;  // Math.Min(MAX_CHARS, nChars);
                }
#endif

                i++;
            }

            return(sheet);

#if NO
            void WriteCell(string text)
            {
                // 统计最大字符数
                // int nChars = column_max_chars[nColIndex - 1];
                if (text != null)
                {
                    DigitalPlatform.dp2.Statis.ClosedXmlUtil.SetMaxChars(column_max_chars, nColIndex - 1, text.Length);
                }
                IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(text, '*'));

                cell.Style.Alignment.WrapText = true;
                cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                // cell.Style.Font.FontName = strFontName;

                /*
                 * if (nColIndex - 1 < alignments.Count)
                 *  cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
                 * else
                 *  cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                 */
                nColIndex++;
            }
#endif
        }