/// <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); }
/// <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> /// Import DataTable, /// BirthDate is formatted /// Active field is set manually /// </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> /// <remarks> /// Unlike example above we will create Demo2 worksheet, import our DataTable /// and do some formatting. Each time we run this code I check to see if Demo2 /// sheet exist, if so, remove and add it back in. /// </remarks> public bool ImportDataTable2(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true) { bool success = false; try { using (SLDocument doc = new SLDocument(pFileName, "Sheet1")) { var helper = new LightHelpers(); if (helper.SheetExists(pFileName, "Demo2")) { doc.DeleteWorksheet("Demo2"); } doc.AddWorksheet("Demo2"); doc.SelectWorksheet("Demo2"); // create a date style for birthday 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(); // there is no method to set all bool values from TRUE/FALSE to Yes/No so we do it manually // we add +1 to EndRowIndex because of the header 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"); } } // set the column header stype var headerSyle = doc.CreateStyle(); headerSyle.Font.FontColor = SysDraw.Color.White; headerSyle.Font.Strike = false; headerSyle.Fill.SetPattern(PatternValues.Solid, SysDraw.Color.Green, SysDraw.Color.White); headerSyle.Font.Underline = UnderlineValues.None; headerSyle.Font.Bold = true; headerSyle.Font.Italic = false; doc.SetCellStyle(1, 1, 1, pDataTable.Columns.Count, headerSyle); // set one column to auto fit //doc.AutoFitColumn(birthDayCellIndex, stats.EndRowIndex + 1); // set all columns to auto fix beginning at data rather than column header doc.AutoFitColumn(2, pDataTable.Columns.Count); // save to the document we opened rather than a new document. doc.Save(); success = true; } } catch (Exception ex) { HasErrors = true; ExceptionMessage = ex.Message; success = false; } return(success); }