public void CreateXls(List <BagiShuDto> Data, string FilePath) { try { var slDocument = new SLDocument(); SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.ScalePage(50); ps.PaperSize = SLPaperSizeValues.FolioPaper; ps.LeftMargin = 0; ps.RightMargin = 0; slDocument.SetPageSettings(ps); //title slDocument.SetCellValue(1, 1, "Detail SHU Koperasi Polowijo Karya Abadi"); slDocument.MergeWorksheetCells(1, 1, 1, 9); slDocument.SetCellValue(2, 1, "Periode " + _intTahun); slDocument.MergeWorksheetCells(2, 1, 2, 9); total_shu.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(3, 1, "Total SHU = Rp. " + total_shu.Text); })); DanaCadangan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(4, 1, "Dana Cadangan (30%) = Rp. " + DanaCadangan.Text); })); JasaModal.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(4, 4, "Jasa Modal (20%) = Rp. " + JasaModal.Text); })); JasaAnggota.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(5, 1, "Jasa Anggota (10%) = Rp. " + JasaAnggota.Text); })); PengurusPengawas.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(5, 4, "Dana Pengawas (10%) = Rp. " + PengurusPengawas.Text); })); KesejahteraanPegawai.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(6, 1, "Kesejahteraan Pegawai (10%) = Rp. " + KesejahteraanPegawai.Text); })); Pendidikan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(6, 4, "Pendidikan (10%) = Rp. " + Pendidikan.Text); })); DanaSosial.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(7, 1, "Dana Sosial (10%) = Rp. " + DanaSosial.Text); })); total_simpanan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(8, 1, "Total Simpanan = Rp. " + total_simpanan.Text); })); total_pinjaman.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(8, 4, "Total Piutang = Rp. " + total_pinjaman.Text); })); total_belanja.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(9, 1, "Total Penjualan = Rp. " + total_belanja.Text); })); //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); valueStyle.Font.Bold = true; valueStyle.Font.FontSize = 16; slDocument.SetCellStyle(1, 1, 2, 1, valueStyle); //create header slDocument = CreateHeaderExcel(slDocument); //create data slDocument = CreateDataExcel(slDocument, Data); var FullPath = FilePath; if (System.IO.File.Exists(FullPath)) { System.IO.File.Delete(FullPath); } slDocument.SaveAs(FullPath); } catch (Exception) { Dialog_Loading.Close(); throw; } }
internal SLWorksheet(List <Color> ThemeColors, List <Color> IndexedColors, double ThemeDefaultColumnWidth, long ThemeDefaultColumnWidthInEMU, int MaxDigitWidth, List <double> ColumnStepSize, double CalculatedDefaultRowHeight) { ForceCustomRowColumnDimensionsSplitting = false; ActiveCell = new SLCellPoint(1, 1); SheetViews = new List <SLSheetView>(); IsDoubleColumnWidth = false; SheetFormatProperties = new SLSheetFormatProperties(ThemeDefaultColumnWidth, ThemeDefaultColumnWidthInEMU, MaxDigitWidth, ColumnStepSize, CalculatedDefaultRowHeight); RowProperties = new Dictionary <int, SLRowProperties>(); ColumnProperties = new Dictionary <int, SLColumnProperties>(); Cells = new Dictionary <SLCellPoint, SLCell>(); HasSheetProtection = false; SheetProtection = new SLSheetProtection(); HasAutoFilter = false; AutoFilter = new SLAutoFilter(); MergeCells = new List <SLMergeCell>(); ConditionalFormattings = new List <SLConditionalFormatting>(); ConditionalFormattings2010 = new List <SLConditionalFormatting2010>(); DataValidations = new List <SLDataValidation>(); DataValidationDisablePrompts = false; DataValidationXWindow = null; DataValidationYWindow = null; Hyperlinks = new List <SLHyperlink>(); PageSettings = new SLPageSettings(ThemeColors, IndexedColors); RowBreaks = new Dictionary <int, SLBreak>(); ColumnBreaks = new Dictionary <int, SLBreak>(); DrawingId = string.Empty; NextWorksheetDrawingId = 2; Pictures = new List <SLPicture>(); Charts = new List <SLChart>(); InitializeBackgroundPictureStuff(); LegacyDrawingId = string.Empty; Authors = new List <string>(); Comments = new Dictionary <SLCellPoint, SLComment>(); Tables = new List <SLTable>(); SparklineGroups = new List <SLSparklineGroup>(); }
protected override void AdditionalSetupExcelDocument() { var pageSetting = new SLPageSettings { ShowGridLines = false, ZoomScale = 65 }; String sheet1Name = DocumentName; document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheet1Name); document.SetPageSettings(pageSetting, sheet1Name); document.SelectWorksheet(sheet1Name); }
static void makeStickers(List <Unit> units, string spName) { using (SLDocument doc = new SLDocument()) { var style_12 = new SLStyle(); style_12.SetFont("arial", 12); style_12.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center); style_12.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center); SLPageSettings ps = new SLPageSettings(); ps.TopMargin = 0; ps.BottomMargin = 0; ps.LeftMargin = 0; ps.RightMargin = 0; doc.SetPageSettings(ps); int col = 1; int row = 1; foreach (Unit unit in units) { for (int qty = 0; qty < unit.qty; qty++) { if (col == 5) { col = 1; row++; } doc.SetColumnWidth(col, COLUMN_WIDTH_FOR_ARIAL_14); doc.SetRowHeight(row, ROW_HEIGHT_FOR_ARIAL_14); doc.SetCellStyle(row, col, style_12); doc.SetCellValue(row, col++, unit.GetSticker()); } } using (var dlg = new SaveFileDialog()) { dlg.Title = "Сохранение файла наклеек"; dlg.FileName = "Наклейки -- " + spName.Split('\\').Last(); var res = dlg.ShowDialog(); if (res != DialogResult.OK) { return; } doc.SaveAs(dlg.FileName); Console.WriteLine("Сохранено в " + dlg.FileName); } } }
public void CreateXls(List <IptAnggotaDto> Data, string FilePath) { try { var slDocument = new SLDocument(); SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.ScalePage(50); ps.PaperSize = SLPaperSizeValues.FolioPaper; ps.LeftMargin = 0; ps.RightMargin = 0; slDocument.SetPageSettings(ps); //title slDocument.SetCellValue(1, 1, "Detail SHU Koperasi Polowijo Karya Abadi"); slDocument.MergeWorksheetCells(1, 1, 1, 9); slDocument.SetCellValue(2, 1, "Periode 2020"); slDocument.MergeWorksheetCells(2, 1, 2, 9); //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); valueStyle.Font.Bold = true; valueStyle.Font.FontSize = 16; slDocument.SetCellStyle(1, 1, 2, 1, valueStyle); //create header slDocument = CreateHeaderExcel(slDocument); //create data slDocument = CreateDataExcel(slDocument, Data); var FullPath = FilePath; if (System.IO.File.Exists(FullPath)) { System.IO.File.Delete(FullPath); } slDocument.SaveAs(FullPath); } catch (Exception) { Dialog_Loading.Close(); throw; } }
private void mergeButton_Click(object sender, EventArgs e) { bool bRC; int nInfoRow = nFirstRow - 4; //int itemCnt = 0; string fullTemplateName = Path.Combine(Properties.Settings.Default.LastTemplatePath, sourceTemplateInput.Text); bRC = sl.SelectWorksheet("Form1"); string partNo = sl.GetCellValueAsString(nInfoRow, 2); string partName = sl.GetCellValueAsString(nInfoRow, 4); string reportNo = sl.GetCellValueAsString(nInfoRow, 8); string serialNo = String.Empty; //SLDocument sl = new SLDocument(fullTemplateName, "Form1"); SLStyle styleFloat = sl.CreateStyle(); styleFloat.FormatCode = fontFormat; styleFloat.SetFontColor(System.Drawing.Color.Black); styleFloat.Font.FontName = fontName; styleFloat.Font.FontSize = Convert.ToInt16(fontSize); styleFloat.Alignment.Horizontal = HorizontalAlignmentValues.Center; styleFloat.Alignment.Vertical = VerticalAlignmentValues.Center; styleFloat.SetHorizontalAlignment(HorizontalAlignmentValues.Center); SLStyle styleFloatV2 = sl.CreateStyle(); styleFloatV2.Font.FontName = fontName; styleFloatV2.Font.FontSize = Convert.ToInt16(fontSize); styleFloatV2.Alignment.Horizontal = HorizontalAlignmentValues.Center; styleFloatV2.Alignment.Vertical = VerticalAlignmentValues.Center; styleFloatV2.SetHorizontalAlignment(HorizontalAlignmentValues.Center); SLStyle styleFloatV2LJ = sl.CreateStyle(); styleFloatV2LJ.Font.FontName = fontName; styleFloatV2LJ.Font.FontSize = Convert.ToInt16(fontSize); styleFloatV2LJ.Alignment.Horizontal = HorizontalAlignmentValues.Left; styleFloatV2LJ.Alignment.Vertical = VerticalAlignmentValues.Center; styleFloatV2LJ.SetHorizontalAlignment(HorizontalAlignmentValues.Left); SLStyle styleFloatPass = sl.CreateStyle(); styleFloatPass.FormatCode = fontFormat; //styleFloatPass.Fill.SetPatternBackgroundColor(System.Drawing.Color.LightGreen); styleFloatPass.SetFontColor(System.Drawing.Color.Black); styleFloatPass.Font.FontName = fontName; styleFloatPass.Font.FontSize = Convert.ToInt16(fontSize); styleFloatPass.Font.Bold = false; styleFloatPass.Font.Italic = false; styleFloatPass.Alignment.Horizontal = HorizontalAlignmentValues.Center; styleFloatPass.Alignment.Vertical = VerticalAlignmentValues.Center; styleFloatPass.SetHorizontalAlignment(HorizontalAlignmentValues.Center); styleFloatPass.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.White, System.Drawing.Color.White); SLStyle styleFloatDev = sl.CreateStyle(); styleFloatDev.FormatCode = fontFormat; styleFloatDev.SetFontColor(System.Drawing.Color.Black); styleFloatDev.Font.FontName = fontName; styleFloatDev.Font.FontSize = Convert.ToInt16(fontSize); styleFloatDev.Font.Bold = false; styleFloatDev.Font.Italic = false; styleFloatDev.Alignment.Horizontal = HorizontalAlignmentValues.Center; styleFloatDev.Alignment.Vertical = VerticalAlignmentValues.Center; styleFloatDev.SetHorizontalAlignment(HorizontalAlignmentValues.Center); styleFloatDev.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightPink, System.Drawing.Color.Red); SLStyle styleString = sl.CreateStyle(); styleString.SetFontColor(System.Drawing.Color.Black); styleString.Font.FontName = fontName; styleString.Font.FontSize = Convert.ToInt16(fontSize); // foreach (resultGrid grid in grids.Values) foreach (resultGrid grid in fileToGrid.Values) { int nRow; TabPage tabPage = grid.tabPage; DataGridView gridCtl = grid.gridControl; string runNo = grid.runNo; string title = tabPage.Text; bRC = sl.SelectWorksheet("Form1"); bRC = sl.CopyWorksheet("Form3", title); bRC = sl.SelectWorksheet(title); // set this sheets serial number serialNo = String.Format("{0}-{1}", partNo, runNo); sl.SetCellValue(nInfoRow, 12, serialNo); foreach (DataGridViewRow row in gridCtl.Rows) { if (row.IsNewRow) { continue; } string charNo = row.Cells["CharNo"].Value.ToString(); string nominal = row.Cells["Nominal"].Value.ToString(); string upper = row.Cells["Upper"].Value.ToString(); string lower = row.Cells["Lower"].Value.ToString(); string actual = row.Cells["Actual"].Value.ToString(); string deviation = row.Cells["Deviation"].Value.ToString(); nRow = charInfos[charNo].charRow; // Actual double actualVal; if (actual.Length > 0) { actualVal = Math.Abs(Convert.ToDouble(actual)); sl.SetCellValue(nRow, 9, actualVal); sl.SetCellStyle(nRow, 9, styleFloat); } sl.SetCellValue(nRow, 9, actual); sl.SetCellStyle(nRow, 9, styleFloat); sl.SetCellStyle(nRow, 2, styleFloatV2); sl.SetCellStyle(nRow, 4, styleFloatV2LJ); sl.SetCellStyle(nRow, 5, styleFloatV2LJ); sl.SetCellStyle(nRow, 6, styleFloatV2); sl.SetCellStyle(nRow, 7, styleFloatV2); sl.SetCellStyle(nRow, 8, styleFloatV2); // Exceed double exceed = 0; if (deviation.Length > 0) { exceed = Math.Abs(Convert.ToDouble(deviation)); sl.SetCellValue(nRow, 11, exceed); sl.SetCellStyle(nRow, 11, styleFloatDev); } else { if (charInfos[charNo].isBasic) { string sRequirement = sl.GetCellValueAsString(nRow, 5); if (sRequirement.StartsWith("(")) { sl.SetCellValue(nRow, 11, "REFERENCE"); } else { sl.SetCellValue(nRow, 11, "BASIC"); } } else { sl.SetCellValue(nRow, 11, "PASS"); } sl.SetCellStyle(nRow, 11, styleFloatPass); } } } if (statsIncluded) { SLPageSettings ps = sl.GetPageSettings("CMMStats"); bRC = sl.SelectWorksheet("CMMStats"); if (bRC) { sl.SetCellValue(nInfoRow, 2, partNo); sl.SetCellValue(nInfoRow, 6, partName); //sl.SetCellValue(nInfoRow, 12, serialNo); sl.SetCellValue(nInfoRow, 13, reportNo); int nRow = nFirstRow; foreach (DataGridViewRow row in statsGrid.gridControl.Rows) { if (row.IsNewRow) { continue; } //CharNo, Nominal, Mean, Variance, StdDev string charNo = row.Cells["CharNo"].Value.ToString(); string nominal = row.Cells["Nominal"].Value.ToString(); string mean = row.Cells["Mean"].Value.ToString(); string variance = row.Cells["Variance"].Value.ToString(); string stdDev = row.Cells["StdDev"].Value.ToString(); string desc = charInfos[charNo].desc; if (nRow > (nFirstRow + 20)) { sl.InsertRow(nRow, 1); sl.MergeWorksheetCells(nRow, 3, nRow, 9); sl.CopyRowStyle(nFirstRow, nRow); } sl.SetCellValue(nRow, 2, charNo); sl.SetCellStyle(nRow, 2, sl.GetCellStyle(nFirstRow, 2)); sl.SetCellValue(nRow, 3, desc); sl.SetCellStyle(nRow, 3, sl.GetCellStyle(nFirstRow, 3)); sl.SetCellStyle(nRow, 4, sl.GetCellStyle(nFirstRow, 4)); sl.SetCellStyle(nRow, 5, sl.GetCellStyle(nFirstRow, 5)); sl.SetCellStyle(nRow, 6, sl.GetCellStyle(nFirstRow, 6)); sl.SetCellStyle(nRow, 7, sl.GetCellStyle(nFirstRow, 7)); sl.SetCellStyle(nRow, 8, sl.GetCellStyle(nFirstRow, 8)); sl.SetCellStyle(nRow, 9, sl.GetCellStyle(nFirstRow, 9)); sl.SetCellValue(nRow, 10, nominal); sl.SetCellStyle(nRow, 10, sl.GetCellStyle(nFirstRow, 10)); sl.SetCellValue(nRow, 11, mean); sl.SetCellStyle(nRow, 11, sl.GetCellStyle(nFirstRow, 11)); sl.SetCellValue(nRow, 12, variance); sl.SetCellStyle(nRow, 12, sl.GetCellStyle(nFirstRow, 12)); sl.SetCellValue(nRow, 13, stdDev); sl.SetCellStyle(nRow, 13, sl.GetCellStyle(nFirstRow, 13)); nRow++; } sl.SetPageSettings(ps); } } else { sl.SelectWorksheet("Form1"); sl.DeleteWorksheet("CMMStats"); } sl.SelectWorksheet("Form1"); sl.DeleteWorksheet("Form3"); string xlsFilename = Path.Combine(Properties.Settings.Default.LastReportPath, targetInput.Text); sl.SaveAs(xlsFilename); MessageBox.Show(String.Format("Result data merge complete: \n\r{0}", xlsFilename), "FAI Report"); }
private void GeraArquivo(System.Data.DataTable dt, string dataInicial, string dataFinal) { SLDocument sl = new SLDocument(); int linha = 1; //Set das configurações do arquivos e variaveis de style SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); SLStyle bordaAcima = sl.CreateStyle(); bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultBold = sl.CreateStyle(); resultBold.SetFontBold(true); resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultRed = sl.CreateStyle(); resultRed.SetFontBold(true); resultRed.SetFontColor(System.Drawing.Color.Red); resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle bordaContorno = sl.CreateStyle(); bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle alinhadoCentro = sl.CreateStyle(); alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center; alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center; alinhadoCentro.SetWrapText(true); sl.SetCellValue(linha, 1, "Código Empresa"); sl.SetCellValue(linha, 2, "Número do registro do empregado"); sl.SetCellValue(linha, 3, "Nome"); sl.SetCellValue(linha, 4, "E-mail"); sl.SetCellValue(linha, 5, "Data da compra"); sl.SetCellValue(linha, 6, "Valor da compra"); sl.SetCellValue(linha, 7, "Quantidade de ingressos"); sl.SetCellValue(linha, 8, "Parcelas"); sl.SetCellValue(linha, 9, "Forma de pagamento"); sl.SetCellValue(linha, 10, "Data de vencimento"); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); sl.SetCellStyle(linha, 5, bordaContorno); sl.SetCellStyle(linha, 6, bordaContorno); sl.SetCellStyle(linha, 7, bordaContorno); sl.SetCellStyle(linha, 8, bordaContorno); sl.SetCellStyle(linha, 9, bordaContorno); sl.SetCellStyle(linha, 10, bordaContorno); sl.SetCellStyle(linha, 1, resultBold); sl.SetCellStyle(linha, 2, resultBold); sl.SetCellStyle(linha, 3, resultBold); sl.SetCellStyle(linha, 4, resultBold); sl.SetCellStyle(linha, 5, resultBold); sl.SetCellStyle(linha, 6, resultBold); sl.SetCellStyle(linha, 7, resultBold); sl.SetCellStyle(linha, 8, resultBold); sl.SetCellStyle(linha, 9, resultBold); sl.SetCellStyle(linha, 10, resultBold); linha++; foreach (DataRow dr in dt.Rows) { sl.SetCellValue(linha, 1, dr[0].ToString()); sl.SetCellValue(linha, 2, dr[1].ToString()); sl.SetCellValue(linha, 3, dr[2].ToString()); sl.SetCellValue(linha, 4, dr[3].ToString()); sl.SetCellValue(linha, 5, dr[4].ToString()); sl.SetCellValue(linha, 6, dr[5].ToString()); sl.SetCellValue(linha, 7, dr[6].ToString()); sl.SetCellValue(linha, 8, dr[7].ToString()); sl.SetCellValue(linha, 9, dr[8].ToString()); sl.SetCellValue(linha, 10, dr[9].ToString()); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); sl.SetCellStyle(linha, 5, bordaContorno); sl.SetCellStyle(linha, 6, bordaContorno); sl.SetCellStyle(linha, 7, bordaContorno); sl.SetCellStyle(linha, 8, bordaContorno); sl.SetCellStyle(linha, 9, bordaContorno); sl.SetCellStyle(linha, 10, bordaContorno); linha++; } sl.AutoFitColumn(1); sl.AutoFitColumn(2); sl.AutoFitColumn(3); sl.AutoFitColumn(4); sl.AutoFitColumn(5); sl.AutoFitColumn(6); sl.AutoFitColumn(7); sl.AutoFitColumn(8); sl.AutoFitColumn(9); sl.AutoFitColumn(10); string arquivo; if (!String.IsNullOrEmpty(dataInicial) && !String.IsNullOrEmpty(dataFinal)) { arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados " + dataInicial + " á " + dataFinal + ".xlsx"); } else { arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados_Geral.xlsx"); } sl.SaveAs(arquivo); }
protected void geraArquivo(DataTable dt, int tipo) { SLDocument documento = new SLDocument(); int linha = 1; SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; documento.SetPageSettings(ps); SLStyle row = new SLStyle(); row.SetFontBold(true); documento.SetCellValue(linha, 1, "NUM_BORDERO"); documento.SetCellValue(linha, 2, "DT_BORDERO"); documento.SetCellValue(linha, 3, "TIPO"); documento.SetCellValue(linha, 4, "BANCO_BORD"); documento.SetCellValue(linha, 5, "AGENCIA_BORD"); documento.SetCellValue(linha, 6, "NUMCONTA_BORD"); documento.SetCellValue(linha, 7, "PREFIXO"); documento.SetCellValue(linha, 8, "NUM_TITULO"); documento.SetCellValue(linha, 9, "PARCELA"); documento.SetCellValue(linha, 10, "FORNECEDOR"); documento.SetCellValue(linha, 11, "NOME"); documento.SetCellValue(linha, 12, "CNPJ"); documento.SetCellValue(linha, 13, "DT_EMISSAO"); documento.SetCellValue(linha, 14, "DT_VENCIMENTO"); documento.SetCellValue(linha, 15, "VLR_BRUTO"); documento.SetCellValue(linha, 16, "VLR_ISS"); documento.SetCellValue(linha, 17, "VLR_IRRF"); documento.SetCellValue(linha, 18, "VLR_PIS"); documento.SetCellValue(linha, 19, "VLR_COFINS"); documento.SetCellValue(linha, 20, "VLR_CSLL"); documento.SetCellValue(linha, 21, "VLR_LIQ"); documento.SetCellValue(linha, 22, "COD_NATUREZA"); documento.SetCellValue(linha, 23, "DESC_NATUREZA"); documento.SetCellValue(linha, 24, "CONTA_CONTABIL"); documento.SetCellValue(linha, 25, "CENTRO_CUSTO"); documento.SetCellValue(linha, 26, "PATROCINADOR"); documento.SetCellValue(linha, 27, "PLANO"); documento.SetCellValue(linha, 28, "SUBMASSA"); documento.SetCellValue(linha, 29, "VALOR_RATEADO"); documento.SetCellValue(linha, 30, "PERCENTUAL_RATEADO"); documento.SetCellValue(linha, 31, "COD_FORMA_PAG"); documento.SetCellValue(linha, 32, "DESC_FORMA_LIQUID"); documento.SetCellValue(linha, 33, "COD_BARRAS"); documento.SetCellValue(linha, 34, "PROJETO"); documento.SetRowStyle(linha, row); linha++; foreach (DataRow dr in dt.Rows) { documento.SetCellValue(linha, 1, dr[0].ToString()); documento.SetCellValue(linha, 2, string.IsNullOrEmpty(dr[1].ToString()) ? "" : Convert.ToDateTime(dr[1]).ToShortDateString()); documento.SetCellValue(linha, 3, dr[2].ToString()); documento.SetCellValue(linha, 4, dr[3].ToString()); documento.SetCellValue(linha, 5, dr[4].ToString()); documento.SetCellValue(linha, 6, dr[5].ToString()); documento.SetCellValue(linha, 7, dr[6].ToString()); documento.SetCellValue(linha, 8, dr[7].ToString()); documento.SetCellValue(linha, 9, dr[8].ToString()); documento.SetCellValue(linha, 10, dr[9].ToString()); documento.SetCellValue(linha, 11, dr[10].ToString()); documento.SetCellValue(linha, 12, dr[11].ToString()); documento.SetCellValue(linha, 13, string.IsNullOrEmpty(dr[12].ToString()) ? "" : Convert.ToDateTime(dr[12]).ToShortDateString()); // documento.SetCellValue(linha, 14, string.IsNullOrEmpty(dr[13].ToString()) ? "" : Convert.ToDateTime(dr[13]).ToShortDateString()); documento.SetCellValue(linha, 14, Convert.ToDateTime(dr[13].ToString())); documento.SetCellValue(linha, 15, dr[14].ToString()); documento.SetCellValue(linha, 16, dr[15].ToString()); documento.SetCellValue(linha, 17, dr[16].ToString()); documento.SetCellValue(linha, 18, dr[17].ToString()); documento.SetCellValue(linha, 19, dr[18].ToString()); documento.SetCellValue(linha, 20, dr[19].ToString()); documento.SetCellValue(linha, 21, dr[20].ToString()); documento.SetCellValue(linha, 22, dr[21].ToString()); documento.SetCellValue(linha, 23, dr[22].ToString()); documento.SetCellValue(linha, 24, dr[23].ToString()); documento.SetCellValue(linha, 25, dr[24].ToString()); documento.SetCellValue(linha, 26, dr[25].ToString()); documento.SetCellValue(linha, 27, dr[26].ToString()); documento.SetCellValue(linha, 28, dr[27].ToString()); documento.SetCellValue(linha, 29, dr[28].ToString()); documento.SetCellValue(linha, 30, dr[29].ToString()); documento.SetCellValue(linha, 31, dr[30].ToString()); documento.SetCellValue(linha, 32, dr[31].ToString()); documento.SetCellValue(linha, 33, dr[32].ToString()); documento.SetCellValue(linha, 34, dr[33].ToString()); linha++; } string caminho = ""; if (tipo == 0) { caminho = Server.MapPath(@"UploadFile\\" + "BorderoPagarSemRateio.xls"); } else if (tipo == 1) { caminho = Server.MapPath(@"UploadFile\\" + "BorderoPagarComRateio.xls"); } documento.SaveAs(caminho); //documento.column }
private void GeraArquivoResposta(System.Data.DataTable dt, string mes, string ano) { SLDocument sl = new SLDocument(); SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); int linha = 2, count = 1; //Set do cabeçalho sl.SetCellValue(1, 2, "CHAMADO"); sl.SetCellValue(1, 3, "CHAM_DS_PROTOCOLO"); sl.SetCellValue(1, 4, "MANIFESTAÇÃO"); sl.SetCellValue(1, 5, "AREA"); sl.SetCellValue(1, 6, "ENVIO"); sl.SetCellValue(1, 7, "RESPOSTA"); sl.SetCellValue(1, 8, "DATA INICIAL"); sl.SetCellValue(1, 9, "DATA PREVISAO"); sl.SetCellValue(1, 10, "DATA ENCERRAMENTO"); //Set layout das colunas SLStyle ss = colunaStyleAux(); sl.SetColumnStyle(1, ss); sl.SetColumnStyle(2, ss); sl.SetColumnStyle(3, ss); sl.SetColumnStyle(4, ss); sl.SetColumnStyle(5, ss); sl.SetColumnStyle(6, ss); sl.SetColumnStyle(7, ss); sl.SetColumnStyle(8, ss); sl.SetColumnStyle(9, ss); sl.SetColumnStyle(10, ss); sl.SetColumnWidth(2, 11); sl.SetColumnWidth(3, 22); sl.SetColumnWidth(4, 16); sl.SetColumnWidth(5, 45); sl.SetColumnWidth(6, 11); sl.SetColumnWidth(7, 12); sl.SetColumnWidth(8, 13); sl.SetColumnWidth(9, 16); sl.SetColumnWidth(10, 25); sl.SetRowStyle(1, cabecalhoStyleAux()); foreach (DataRow dr in dt.Rows) { sl.SetCellValue(linha, 1, count); sl.SetCellValue(linha, 2, dr[0].ToString()); sl.SetCellValue(linha, 3, dr[1].ToString()); sl.SetCellValue(linha, 4, dr[2].ToString()); sl.SetCellValue(linha, 5, dr[3].ToString()); sl.SetCellValue(linha, 6, string.IsNullOrEmpty(dr[4].ToString())? "" :Convert.ToDateTime(dr[4]).ToShortDateString()); sl.SetCellValue(linha, 7, string.IsNullOrEmpty(dr[5].ToString()) ? "" : Convert.ToDateTime(dr[5]).ToShortDateString()); sl.SetCellValue(linha, 8, string.IsNullOrEmpty(dr[6].ToString()) ? "" : Convert.ToDateTime(dr[6]).ToShortDateString()); sl.SetCellValue(linha, 9, string.IsNullOrEmpty(dr[7].ToString()) ? "" : Convert.ToDateTime(dr[7]).ToShortDateString()); sl.SetCellValue(linha, 10, string.IsNullOrEmpty(dr[8].ToString()) ? "" : Convert.ToDateTime(dr[8]).ToShortDateString()); linha++; count++; } string caminho = Server.MapPath(@"UploadFile\\" + "aux2-Respostas " + mes + " " + ano + ".xlsx"); sl.SaveAs(caminho); }
private void GeraArquivoEstouros(System.Data.DataTable dt, string mes, string ano) { SLDocument sl = new SLDocument(); SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); int linha = 2, count = 1; //Set do cabeçalho sl.SetCellValue(1, 2, "CHAMADO"); sl.SetCellValue(1, 3, "CHAMADO"); sl.SetCellValue(1, 4, "MANI_NR_SEQUENCIA"); sl.SetCellValue(1, 5, "AREA_DS_AREA"); sl.SetCellValue(1, 6, "AREA"); sl.SetCellValue(1, 7, "RECEBEU"); sl.SetCellValue(1, 8, "REGISTRO"); sl.SetCellValue(1, 9, "RESPONDEU"); sl.SetCellValue(1, 10, "PREVISAO"); sl.SetCellValue(1, 11, "ENCERROU"); sl.SetCellValue(1, 12, "TIPO_MANIFESTACAO"); sl.SetCellValue(1, 13, "GRUPO_MANIFESTACAO"); //Set layout das colunas SLStyle ss = colunaStyleAux(); sl.SetColumnStyle(1, ss); sl.SetColumnStyle(2, ss); sl.SetColumnStyle(3, ss); sl.SetColumnStyle(4, ss); sl.SetColumnStyle(5, ss); sl.SetColumnStyle(6, ss); sl.SetColumnStyle(7, ss); sl.SetColumnStyle(8, ss); sl.SetColumnStyle(9, ss); sl.SetColumnStyle(10, ss); sl.SetColumnStyle(11, ss); sl.SetColumnStyle(12, ss); sl.SetColumnStyle(13, ss); sl.SetColumnWidth(2, 11); sl.SetColumnWidth(3, 20); sl.SetColumnWidth(4, 25); sl.SetColumnWidth(5, 45); sl.SetColumnWidth(6, 36); sl.SetColumnWidth(7, 17); sl.SetColumnWidth(8, 17); sl.SetColumnWidth(9, 17); sl.SetColumnWidth(10, 17); sl.SetColumnWidth(11, 17); sl.SetColumnWidth(12, 30); sl.SetColumnWidth(13, 30); sl.SetRowStyle(1, cabecalhoStyleAux()); foreach (DataRow dr in dt.Rows) { sl.SetCellValue(linha, 1, count); sl.SetCellValue(linha, 2, dr[0].ToString()); sl.SetCellValue(linha, 3, dr[1].ToString()); sl.SetCellValue(linha, 4, dr[2].ToString()); sl.SetCellValue(linha, 5, dr[3].ToString()); sl.SetCellValue(linha, 6, dr[4].ToString()); sl.SetCellValue(linha, 7, dr[5].ToString()); sl.SetCellValue(linha, 8, dr[6].ToString()); sl.SetCellValue(linha, 9, dr[7].ToString()); sl.SetCellValue(linha, 10, dr[8].ToString()); sl.SetCellValue(linha, 11, dr[9].ToString()); sl.SetCellValue(linha, 12, dr[10].ToString()); sl.SetCellValue(linha, 13, dr[11].ToString()); linha++; count++; } string caminho = Server.MapPath(@"UploadFile\\" + "aux1-Estouros " + mes + " " + ano + ".xlsx"); sl.SaveAs(caminho); }
private void GeraArquivoGeral(DataSet ds, string mes, string ano) { string dtInicio = Convert.ToDateTime(txtDtInicioRelEstouro.Text).ToString("dd-MM-yyyy"); string dtFinal = Convert.ToDateTime(txtDtFinalRelEstouro.Text).ToString("dd-MM-yyyy"); int qtdestouro = 0, resperiodo = 0, linha = 6; int previsao = objBLL.GeraTotalEstouros(Convert.ToDateTime(dtInicio), Convert.ToDateTime(dtFinal)); SLDocument sl = new SLDocument(); //Set das configurações do arquivos e variaveis de style SLPageSettings ps = new SLPageSettings(); ps.Orientation = OrientationValues.Landscape; ps.PaperSize = SLPaperSizeValues.A4Paper; sl.SetPageSettings(ps); SLStyle bordaAcima = sl.CreateStyle(); bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultBold = sl.CreateStyle(); resultBold.SetFontBold(true); resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle resultRed = sl.CreateStyle(); resultRed.SetFontBold(true); resultRed.SetFontColor(System.Drawing.Color.Red); resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle bordaContorno = sl.CreateStyle(); bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black); SLStyle alinhadoCentro = sl.CreateStyle(); alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center; alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center; alinhadoCentro.SetWrapText(true); //Loops de preenchimento do arquivo foreach (DataRow LAreaRel in ds.Tables["AREAS_REL"].Rows) { foreach (DataRow LQtdEstouro in ds.Tables["QTD_ESTOURO"].Rows) { if (LAreaRel[0].ToString() == LQtdEstouro[0].ToString()) { qtdestouro = Convert.ToInt32(LQtdEstouro[1]); break; } qtdestouro = 0; } foreach (DataRow LRespPeriodo in ds.Tables["RESP_PERIODO"].Rows) { if (LAreaRel[0].ToString() == LRespPeriodo[0].ToString()) { resperiodo = Convert.ToInt32(LRespPeriodo[1]); break; } resperiodo = 0; } sl.SetCellValue(linha, 1, LAreaRel[0].ToString()); sl.SetCellValue(linha, 2, qtdestouro); sl.SetCellValue(linha, 3, resperiodo); sl.SetCellStyle(linha, 1, bordaContorno); sl.SetCellStyle(linha, 2, bordaContorno); sl.SetCellStyle(linha, 3, bordaContorno); sl.SetCellStyle(linha, 4, bordaContorno); if (qtdestouro != 0 && resperiodo != 0) { sl.SetCellValue(linha, 4, "=ROUND(((B" + linha + "/C" + linha + ")*100),2)"); } else { sl.SetCellValue(linha, 4, 0); } linha++; } //Atribuindo valores fixos e stylo das celulas int auxlinha = linha - 1; sl.SetCellValue(1, 1, "Manifestações com previsão de encerramento entre " + dtInicio + " e " + dtFinal); sl.SetCellValue(1, 2, previsao.ToString()); sl.SetCellStyle(1, 1, alinhadoCentro); sl.SetCellStyle(1, 2, alinhadoCentro); sl.SetCellValue(2, 1, "Respostas das Áreas Gerenciadoras:"); sl.SetCellValue(2, 2, "=C" + linha + @"&"" ou ""&ROUND(B1/C" + linha + @",4)*100&""%"""); sl.SetCellValue(3, 1, "Manifestações encerradas após a previsão:"); sl.SetCellValue(3, 2, "=B" + linha + @"&"" ou ""&ROUND(B" + linha + @"/B1,4)*100&""%"""); sl.SetCellValue(5, 1, "Áreas Gerenciadoras"); sl.SetCellValue(5, 2, "Quantidade de estouros dos prazos"); sl.SetCellValue(5, 3, "Respostas no período"); sl.SetCellValue(5, 4, "%"); sl.SetCellStyle(5, 1, resultBold); sl.SetCellStyle(5, 2, resultBold); sl.SetCellStyle(5, 3, resultBold); sl.SetCellStyle(5, 4, resultBold); sl.SetCellValue(linha, 1, "Total"); sl.SetCellValue(linha, 2, "=SUM(B6:B" + auxlinha + ")"); sl.SetCellValue(linha, 3, "=SUM(C6:C" + auxlinha + ")"); sl.SetCellStyle(linha, 1, resultBold); sl.SetCellStyle(linha, 2, resultRed); sl.SetCellStyle(linha, 3, resultRed); sl.SetRowHeight(5, 38); sl.SetColumnWidth(1, 39); sl.SetRowHeight(1, 40); sl.AutoFitColumn(2); sl.AutoFitColumn(3); sl.SetColumnWidth(4, 7); sl.SetColumnStyle(1, alinhadoCentro); sl.SetColumnStyle(2, alinhadoCentro); sl.SetColumnStyle(3, alinhadoCentro); sl.SetColumnStyle(4, alinhadoCentro); string caminho = Server.MapPath(@"UploadFile\\" + "Relatorio_Estouro " + mes + " " + ano + ".xlsx"); sl.SaveAs(caminho); }
private void mergeButton_Click(object sender, EventArgs e) { bool bRC; //int itemCnt = 0; string fullTemplateName = Path.Combine(Properties.Settings.Default.LastTemplatePath, sourceTemplateInput.Text); bRC = sl.SelectWorksheet("Form1"); string partNo = sl.GetCellValueAsString(4, 2); string partName = sl.GetCellValueAsString(4, 4); string reportNo = sl.GetCellValueAsString(4, 8); string serialNo = String.Empty; //SLDocument sl = new SLDocument(fullTemplateName, "Form1"); SLStyle styleFloat = sl.CreateStyle(); styleFloat.FormatCode = formatCode.Text; styleFloat.SetFontColor(System.Drawing.Color.Black); styleFloat.Font.FontName = fontName.Text; styleFloat.Font.FontSize = Convert.ToInt16(fontSize.Text); SLStyle styleString = sl.CreateStyle(); styleString.SetFontColor(System.Drawing.Color.Black); styleString.Font.FontName = fontName.Text; styleString.Font.FontSize = Convert.ToInt16(fontSize.Text); foreach (resultGrid grid in grids.Values) { int nRow; TabPage tabPage = grid.tabPage; DataGridView gridCtl = grid.gridControl; string runNo = grid.runNo; string title = tabPage.Text; bRC = sl.SelectWorksheet("Form1"); bRC = sl.CopyWorksheet("Form3", title); bRC = sl.SelectWorksheet(title); // set this sheets serial number serialNo = String.Format("{0}-{1}", partNo, runNo); sl.SetCellValue(4, 12, serialNo); foreach (DataGridViewRow row in gridCtl.Rows) { if (row.IsNewRow) { continue; } string charNo = row.Cells["CharNo"].Value.ToString(); string nominal = row.Cells["Nominal"].Value.ToString(); string upper = row.Cells["Upper"].Value.ToString(); string lower = row.Cells["Lower"].Value.ToString(); string actual = row.Cells["Actual"].Value.ToString(); string deviation = row.Cells["Deviation"].Value.ToString(); nRow = charInfos[charNo].charRow; // Actual double actualVal; if (actual.Length > 0) { actualVal = Math.Abs(Convert.ToDouble(actual)); sl.SetCellValue(nRow, 9, actualVal); sl.SetCellStyle(nRow, 9, styleFloat); } sl.SetCellValue(nRow, 9, actual); sl.SetCellStyle(nRow, 9, styleFloat); // Exceed double exceed = 0; if (deviation.Length > 0) { exceed = Math.Abs(Convert.ToDouble(deviation)); sl.SetCellValue(nRow, 11, exceed); sl.SetCellStyle(nRow, 11, styleFloat); } } } if (statsIncluded) { SLPageSettings ps = sl.GetPageSettings("CMMStats"); bRC = sl.SelectWorksheet("CMMStats"); if (bRC) { sl.SetCellValue(4, 2, partNo); sl.SetCellValue(4, 6, partName); sl.SetCellValue(4, 12, serialNo); sl.SetCellValue(4, 13, reportNo); //styleFloat.Border.LeftBorder.BorderStyle = BorderStyleValues.Medium; //styleFloat.Border.RightBorder.BorderStyle = BorderStyleValues.Medium; //styleFloat.Border.BottomBorder.BorderStyle = BorderStyleValues.Medium; int nRow = 8; foreach (DataGridViewRow row in statsGrid.gridControl.Rows) { if (row.IsNewRow) { continue; } //CharNo, Nominal, Mean, Variance, StdDev string charNo = row.Cells["CharNo"].Value.ToString(); string nominal = row.Cells["Nominal"].Value.ToString(); string mean = row.Cells["Mean"].Value.ToString(); string variance = row.Cells["Variance"].Value.ToString(); string stdDev = row.Cells["StdDev"].Value.ToString(); string desc = charInfos[charNo].desc; if (nRow > 28) { sl.InsertRow(nRow, 1); sl.MergeWorksheetCells(nRow, 3, nRow, 9); sl.CopyRowStyle(8, nRow); } sl.SetCellValue(nRow, 2, charNo); sl.SetCellStyle(nRow, 2, sl.GetCellStyle("B8")); sl.SetCellValue(nRow, 3, desc); sl.SetCellStyle(nRow, 3, sl.GetCellStyle("C8")); sl.SetCellStyle(nRow, 4, sl.GetCellStyle("D8")); sl.SetCellStyle(nRow, 5, sl.GetCellStyle("E8")); sl.SetCellStyle(nRow, 6, sl.GetCellStyle("F8")); sl.SetCellStyle(nRow, 7, sl.GetCellStyle("G8")); sl.SetCellStyle(nRow, 8, sl.GetCellStyle("H8")); sl.SetCellStyle(nRow, 9, sl.GetCellStyle("I8")); sl.SetCellValue(nRow, 10, nominal); sl.SetCellStyle(nRow, 10, sl.GetCellStyle("J8")); sl.SetCellValue(nRow, 11, mean); sl.SetCellStyle(nRow, 11, sl.GetCellStyle("K8")); sl.SetCellValue(nRow, 12, variance); sl.SetCellStyle(nRow, 12, sl.GetCellStyle("L8")); sl.SetCellValue(nRow, 13, stdDev); sl.SetCellStyle(nRow, 13, sl.GetCellStyle("M8")); nRow++; } sl.SetPageSettings(ps); } } else { sl.SelectWorksheet("Form1"); sl.DeleteWorksheet("CMMStats"); } sl.SelectWorksheet("Form1"); sl.DeleteWorksheet("Form3"); string xlsFilename = Path.Combine(Properties.Settings.Default.LastReportPath, targetInput.Text); sl.SaveAs(xlsFilename); MessageBox.Show(String.Format("Result data merge complete: \n\r{0}", xlsFilename), "FAI Report"); }
/// <summary> /// Based on ImportDataTable2, added background color to odd rows, /// conditional formatting, in this case the State column. /// </summary> /// <param name="pFileName">Existing excel file</param> /// <param name="pStartReference">Row to start import</param> /// <param name="pDataTable">Data source</param> /// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param> /// <returns></returns> public bool ImportDataTable4(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true) { bool success = false; var sheetName = "Demo4"; try { // open to Sheet1 (will always exists in this code sample) using (SLDocument doc = new SLDocument(pFileName, "Sheet1")) { var helper = new LightHelpers(); if (helper.SheetExists(pFileName, sheetName)) { doc.DeleteWorksheet(sheetName); } // add the sheet doc.AddWorksheet(sheetName); var isSet = doc.SelectWorksheet(sheetName); // next three lines are responsible for removal of grid-lines in the current WorkSheet SLPageSettings ps = new SLPageSettings(); ps.ShowGridLines = false; doc.SetPageSettings(ps); // setup a format for the date column var birthdayColumnStyle = doc.CreateStyle(); birthdayColumnStyle.FormatCode = "mm-dd-yyyy"; // create style for odd rows (we skip the header row) var oddRowStyle = doc.CreateStyle(); oddRowStyle.SetPatternFill(PatternValues.LightGray, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color); var moCellStyle = doc.CreateStyle(); moCellStyle.Font.Bold = true; moCellStyle.Font.FontColor = SysDraw.Color.Red; // since we need these several times, set them up as variables var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1; var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1; // set birthday style to our data doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle); // do the Datatable import. doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders); SLWorksheetStatistics stats = doc.GetWorksheetStatistics(); // change format of the bool column for (int row = 1; row < stats.EndRowIndex + 1; row++) { var value = doc.GetCellValueAsString(row, activeCellIndex); if (value == "TRUE") { doc.SetCellValue(row, activeCellIndex, "Yes"); } else if (value == "FALSE") { doc.SetCellValue(row, activeCellIndex, "No"); } // apply background color style to odd rows if (row.IsOdd() && row > 1) { doc.SetRowStyle(row, oddRowStyle); } else { var cellReference = SLConvert.ToCellReference(row, 6); var stateValue = doc.GetCellValueAsString(cellReference); if (stateValue == "MO") { doc.SetCellStyle(cellReference, moCellStyle); } } } // delete the primary key column doc.DeleteColumn(1, 1); // does what it says, auto-fit cell content doc.AutoFitColumn(2, pDataTable.Columns.Count); // de-select the current sheet and select sheet1 doc.SelectWorksheet("Sheet1"); doc.Save(); success = true; } } catch (Exception ex) { HasErrors = true; ExceptionMessage = ex.Message; success = false; } return(success); }
/// <summary> /// Focus here is on removal of grid-lines while using code from /// ImportDataTable2 method above. Also removed header formatting. /// </summary> /// <param name="pFileName">Existing excel file</param> /// <param name="pStartReference">Row to start import</param> /// <param name="pDataTable">Data source</param> /// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param> /// <returns></returns> public bool ImportDataTable3(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true) { bool success = false; var sheetName = "Demo3"; try { // open to Sheet1 (will always exists in this code sample) using (SLDocument doc = new SLDocument(pFileName, "Sheet1")) { var helper = new LightHelpers(); if (helper.SheetExists(pFileName, sheetName)) { doc.DeleteWorksheet(sheetName); } // add the sheet doc.AddWorksheet(sheetName); var isSet = doc.SelectWorksheet(sheetName); // next three lines are responsible for removal of grid-lines in the current WorkSheet SLPageSettings ps = new SLPageSettings(); ps.ShowGridLines = false; doc.SetPageSettings(ps); // setup a format for the date column var birthdayColumnStyle = doc.CreateStyle(); birthdayColumnStyle.FormatCode = "mm-dd-yyyy"; // since we need these several times, set them up as variables var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1; var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1; // set birthday style to our data doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle); // do the Datatable import. doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders); SLWorksheetStatistics stats = doc.GetWorksheetStatistics(); // change format of the bool column for (int row = 1; row < stats.EndRowIndex + 1; row++) { var value = doc.GetCellValueAsString(row, activeCellIndex); if (value == "TRUE") { doc.SetCellValue(row, activeCellIndex, "Yes"); } else if (value == "FALSE") { doc.SetCellValue(row, activeCellIndex, "No"); } } // does what it says, auto-fit cell content doc.AutoFitColumn(2, pDataTable.Columns.Count); doc.Save(); success = true; } } catch (Exception ex) { HasErrors = true; ExceptionMessage = ex.Message; success = false; } return(success); }