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);
}
