private void MakeHeaders(IXLWorksheet sheet, TestSuite suite) { int column = 1; for (; column <= deep; column++) { string header = string.Empty; for (int i = 1; i < column; i++) header += "Sub-"; sheet.Cell(row, column).Value = header + "Category"; sheet.Column(column).Width = 20.86; } sheet.Cell(row, deep + 1).Value = "Name"; sheet.Column(deep + 1).Width = 30; sheet.Cell(row, deep + 2).Value = "Id"; sheet.Column(deep + 2).Width = 13; sheet.Cell(row, deep + 3).Value = "Summary"; sheet.Column(deep + 3).Width = 42; sheet.Cell(row, deep + 4).Value = "Preconditions"; sheet.Column(deep + 4).Width = 56.43; sheet.Cell(row, deep + 5).Value = "Actions"; sheet.Column(deep + 5).Width = 100; sheet.Cell(row, deep + 6).Value = "Expected Results"; sheet.Column(deep + 6).Width = 100; row++; }
private void SetAutoAdjustColumnForSupplierIn() { _sheetForSupplier.Column(1).AdjustToContents(); _sheetForSupplier.Column(2).AdjustToContents(); _sheetForSupplier.Column(3).AdjustToContents(); _sheetForSupplier.Column(4).AdjustToContents(); }
private void ExportResult(IXLWorksheet ws, Result result) { double time = 0; int index = 2; Console.WriteLine(" - EXPORTING RESULT [ {0}: {1} ]", result.ID, result.Name); ws.Cell(1, 1).Value = "Time"; ws.Cell(1, 2).Value = "Pressure"; ws.Cell(1, 3).Value = "Conditioning"; ws.Cell(1, 4).Value = "VAS"; foreach (var p in result.Data) { ws.Cell(index, 1).Value = time; ws.Cell(index, 2).Value = Math.Round(p.stimulating * 100) / 100.0; ws.Cell(index, 3).Value = Math.Round(p.conditioning * 100) / 100; ws.Cell(index, 4).Value = Math.Round(p.VAS * 100) / 100; ++index; time += 1.0 / 20.0; } ws.Column(1).AdjustToContents(); ws.Column(2).AdjustToContents(); ws.Column(3).AdjustToContents(); ws.Column(4).AdjustToContents(); }
private void AddHeader(IXLWorksheet worksheet, VSProject project, IEnumerable <CultureInfo> cultures) { worksheet.Style.NumberFormat.SetNumberFormatId(49); // Format: Text worksheet.Cell(1, 1).Value = project.Name; worksheet.Cell(1, 2).Value = "Keys"; int c = 3; foreach (var culture in cultures) { if (AutoAdjustLayout) { worksheet.Column(c).Width = ColumnValueWidth; worksheet.Column(c).Style.Alignment.SetWrapText(true); } worksheet.Cell(1, c++).Value = culture.Name; if (ExportComments) { if (AutoAdjustLayout) { worksheet.Column(c).Width = ColumnCommentWidth; worksheet.Column(c).Style.Alignment.SetWrapText(true); } worksheet.Cell(1, c++).Value = culture.Name + " " + COMMENTS_KEY; } } }
public static IXLCell CLSetColHeader(this IXLWorksheet @this, int row, int col, string name, int width = 10, CxTipoCol TipoCol = CxTipoCol.General) { @this.Column(col).Width = width; @this.Column(col).Style.NumberFormat.NumberFormatId = (int)TipoCol; return(@this.Cell(row, col).CLSetColHeader(name, width, TipoCol)); }
private void InitializeWorksheet(IXLWorksheet worksheet) { void SetColumn(string columnIndex, string columnName, float columnWidth) { worksheet.Cell(columnIndex).Value = columnName; worksheet.Column(columnIndex[0] - 'A' + 1).Width = columnWidth; // 'A' -> 1 ; 'C' -> 3 } SetColumn("A1", "Poster", 8.11f); SetColumn("B1", "AnimeName", 36.8f); SetColumn("C1", "Rating", 8.11f); SetColumn("D1", "StudioName", 16.5f); SetColumn("E1", "Status", 12.8f); SetColumn("F1", "AgeRating", 40.7f); SetColumn("G1", "Type", 25f); SetColumn("H1", "Description", 19.2f); SetColumn("I1", "Source", 10.8f); SetColumn("J1", "Season", 10.1f); SetColumn("K1", "Genres", 10.4f); worksheet.Row(1).Style.Font.Bold = true; worksheet.Columns(1, 11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Column(8).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left); worksheet.Column(11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left); worksheet.Range("A1", "K1").Style.Fill.BackgroundColor = XLColor.FromArgb(112, 173, 71); }
private void createMajorIntervalWorkheet(IXLWorksheet worksheet) { worksheet.Column("A").Width = 20; worksheet.Column("B").Width = 20; worksheet.Column("C").Width = 20; worksheet.Row(1).Height = 22.5; worksheet.Row(1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); worksheet.Row(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); worksheet.Row(1).Style.Font.Bold = true; worksheet.Cell("A1").Value = "DATE"; addRightBottomBorder(worksheet.Cell("A1")); worksheet.Cell("B1").Value = "TIME"; addRightBottomBorder(worksheet.Cell("B1")); worksheet.Cell("C1").Value = "dB LAeq,t"; addBottomBorder(worksheet.Cell("C1")); int index = 1; foreach (var r in readings.Where(x => x.Major).OrderBy(x => x.Time)) { index++; worksheet.Cell("A" + index).Value = r.Time.ToString("dd/MM/yy"); addRightBottomBorder(worksheet.Cell("A" + index)); worksheet.Cell("B" + index).Value = r.Time.ToString("HH:mm:ss"); addRightBottomBorder(worksheet.Cell("B" + index)); worksheet.Cell("C" + index).Value = Math.Round(r.Data.LAeq, 1).ToString(); addRightBottomBorder(worksheet.Cell("C" + index)); worksheet.Row(index).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); } }
public void MovingRanges() { var wb = new XLWorkbook(); IXLWorksheet sheet1 = wb.Worksheets.Add("Sheet1"); IXLWorksheet sheet2 = wb.Worksheets.Add("Sheet2"); wb.NamedRanges.Add("wbNamedRange", "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); sheet1.NamedRanges.Add("sheet1NamedRange", "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); sheet2.NamedRanges.Add("sheet2NamedRange", "Sheet1!A1,Sheet2!A1"); sheet1.Row(1).InsertRowsAbove(2); sheet1.Row(1).Delete(); sheet1.Column(1).InsertColumnsBefore(2); sheet1.Column(1).Delete(); Assert.AreEqual("Sheet1!$C$3,Sheet1!$C$4:$D$4,Sheet2!$D$3:$D$4,Sheet1!$7:$8,Sheet1!$G:$H", wb.NamedRanges.First().RefersTo); Assert.AreEqual("Sheet1!$C$3,Sheet1!$C$4:$D$4,Sheet2!$D$3:$D$4,Sheet1!$7:$8,Sheet1!$G:$H", sheet1.NamedRanges.First().RefersTo); Assert.AreEqual("Sheet1!B2,Sheet2!A1", sheet2.NamedRanges.First().RefersTo); wb.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Workbook, nr.Scope)); sheet1.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Worksheet, nr.Scope)); sheet2.NamedRanges.ForEach(nr => Assert.AreEqual(XLNamedRangeScope.Worksheet, nr.Scope)); }
internal static void ExportTableData(IXLWorksheet worksheet, DataTable data, int firstRow, int firstColumn) { int lastRow = data.Rows.Count + firstRow; int lastColumn = data.Columns.Count + firstColumn + 1; int numRow = lastRow - firstRow + 1; int numColumn = lastColumn - firstColumn + 1; int lastValue = firstRow + 1; IXLRange rangeValues = worksheet.Range(firstRow + 1, firstColumn + 4, lastRow, lastColumn - 1); rangeValues.Style.NumberFormat.NumberFormatId = 3; rangeValues = worksheet.Range(firstRow + 1, lastColumn, lastRow, lastColumn); rangeValues.Style.NumberFormat.NumberFormatId = 10; rangeValues = worksheet.Range(firstRow + 1, firstColumn, lastRow, firstColumn + 3); rangeValues.Style.Font.SetBold(true); rangeValues = worksheet.Range(firstRow, lastColumn - 1, lastRow, lastColumn); rangeValues.Style.Font.SetBold(true); rangeValues = worksheet.Range(firstRow, firstColumn, firstRow, lastColumn); rangeValues.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); rangeValues.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); rangeValues.Style.Font.SetBold(true); // Data Table worksheet.Cell(firstRow, firstColumn).Value = "Posición"; foreach (DataColumn column in data.Columns) { int actualColumn = column.Ordinal + firstColumn + 1; worksheet.Cell(firstRow, actualColumn).Value = column.ColumnName; worksheet.Column(actualColumn).Width = 10.71; } worksheet.Cell(firstRow, lastColumn).Value = "% Victorias"; worksheet.Column(lastColumn).Width = 10.00; worksheet.Cell(firstRow + 1, firstColumn + 1).Value = data; worksheet.Column(firstColumn + 1).AdjustToContents(); IXLRange range = worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); range.Style.Border.SetInsideBorder(XLBorderStyleValues.Thin); range.Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin); var table = range.CreateTable(); table.Theme = XLTableTheme.TableStyleMedium16; table.ShowAutoFilter = false; for (int i = 1; i < numRow; i++) { int actRow = firstRow + i; worksheet.Cell(actRow, firstColumn).FormulaA1 = "RANK(H" + actRow + ",$H$" + (firstRow + 1) + ":$H$" + lastRow + ",0)"; worksheet.Cell(actRow, lastColumn).FormulaA1 = "F" + actRow + "/E" + actRow; } }
public async Task <XLWorkbook> WriteDataToFile() { string path = ""; string actualPath = path.SetDirectoryPath(); string newlyCreatedFilePath = $@"{actualPath}\ExcelFiles\ClosedXMLGeneratedFile.xlsx"; var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Primary", 1); //IXLWorksheet ws2 = wb.Worksheets.Add("Secondary", 2); var dataTable = await ReadExcelDataAsync(); ws.Range(1, 1, 1, 5).Merge().AddToNamed("Titles"); //ws2.Range(1, 1, 1, 5).Merge().AddToNamed("Workbook"); var rangeWithData = ws.Cell(2, 1).InsertData(dataTable.AsEnumerable()); //var rangeWithData2 = ws2.Cell(2, 1).InsertData(dataTable.AsEnumerable()); ws.Column(1).SetDataType(XLDataType.Number); ws.Column(2).SetDataType(XLDataType.Text); ws.Column(3).SetDataType(XLDataType.Boolean); ws.Column(4).SetDataType(XLDataType.Text); ws.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy"; //ws2.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy"; //Adjust column widths to their content ws.Columns(1, 5).AdjustToContents(); //ws2.Columns(1, 5).AdjustToContents(); // Prepare the style var dataStyle = ws.Style; dataStyle.Alignment.Vertical = XLAlignmentVerticalValues.Center; // wingdings column var rangeForWingDings = ws.Range(2, 6, 100000, 6).AddToNamed("wingdings"); rangeForWingDings.Value = char.ConvertFromUtf32(0x00002713); rangeForWingDings.Style.Fill.BackgroundColor = XLColor.Red; // Merge cells var mergedCellrange = ws.Range(2, 10, 2, 12); mergedCellrange.Cell(1, 1).Value = "merged"; mergedCellrange.Merge(); // outside border var outsideBorderRange = ws.Range(2, 10, 2, 12); outsideBorderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Double; wb.SaveAs(newlyCreatedFilePath); return(wb); }
public static IXLCell CLSetColHeader(this IXLWorksheet @this, string colRow, string name, int width = 10, CxTipoCol TipoCol = CxTipoCol.General) { var col = colRow.Substring(0, 1); @this.Column(col).Width = width; @this.Column(col).Style.NumberFormat.NumberFormatId = (int)TipoCol; return(@this.Cell(colRow).CLSetColHeader(name, width, TipoCol)); }
private static void setColumnWidths(IXLWorksheet ws) { ws.Columns().Width = 5.22; ws.Column(1).Width = 6.89; ws.Column(2).Width = 6.89; ws.Column(3).Width = 6.89; ws.Column(4).Width = 1.67; }
private void SetAutoAdjustColumnToContent() { _sheet.Column(1).AdjustToContents(); _sheet.Column(2).AdjustToContents(); _sheet.Column(3).AdjustToContents(); _sheet.Column(4).AdjustToContents(); _sheet.Column(5).AdjustToContents(); }
private void WriteColumns(CremaDataTable dataTable, IXLWorksheet sheet) { var index = 1; var rowIndex = 1; if (this.settings.OmitAttribute == false) { sheet.Cell(rowIndex, index++).Value = CremaSchema.Tags; sheet.Cell(rowIndex, index++).Value = CremaSchema.Enable; } foreach (var item in this.GetSortedColumn(dataTable)) { var column = sheet.Column(index); var cell = sheet.Cell(rowIndex, index++); cell.Value = item.ColumnName; if (item.Comment != string.Empty) { cell.Comment.AddText(item.Comment); } if (item.IsKey == true) { cell.Style.Font.Bold = true; } if (item.Unique == true) { cell.Style.Font.Italic = true; } cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; if (item.DerivedTags.Color != null) { column.Style.Fill.BackgroundColor = XLColor.FromHtml(item.DerivedTags.Color); } } if (dataTable.Parent != null) { var column = sheet.Column(index); var cell = sheet.Cell(rowIndex, index++); cell.Value = CremaSchema.RelationID; cell.Comment.AddText("부모 자식 테이블의 관계값을 나타냅니다. 이 열의 값은 부모 시트의 행 번호를 나타냅니다."); cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; column.Style.Border.LeftBorder = XLBorderStyleValues.Thin; column.Style.Border.RightBorder = XLBorderStyleValues.Thin; } if (this.settings.OmitSignatureDate == false) { sheet.Cell(rowIndex, index++).Value = CremaSchema.Creator; sheet.Cell(rowIndex, index++).Value = CremaSchema.CreatedDateTime; sheet.Cell(rowIndex, index++).Value = CremaSchema.Modifier; sheet.Cell(rowIndex, index++).Value = CremaSchema.ModifiedDateTime; } }
public void ProcessRequest(HttpContext context) { var cnn = context.Request.QueryString["cnn"]; var mvx = new CMvx(); mvx.RegisterSingleton <IConfiguracionDeConexion, frmMainMenu.ConfiguracionDeConexion>(new frmMainMenu.ConfiguracionDeConexion(cnn)); mvx.RegisterType <IBaseDeDatosServicio, BaseDeDatosServicio>(); mvx.RegisterType <IOrdenDeVentaServicio, OrdenDeVentaServicio>(); XLWorkbook workbook = new XLWorkbook(); IXLWorksheet worksheet = workbook.Worksheets.Add("Detalle de Orden de Venta"); worksheet.Protect("Mobility2016$$"); var servicio = mvx.Resolve <IOrdenDeVentaServicio>(); var templateExcel = servicio.GenerarColumnasParaTemplate(); int i = 1; foreach (var columna in templateExcel) { worksheet.Cell(1, i).Value = columna.VALUE; worksheet.Column(i).Style.Protection.SetLocked(false); worksheet.Column(i).Width = 25; if (columna.VALUE == "Fecha de entrega") { worksheet.Column(i).Style.NumberFormat.Format = "dd/mm/yyyy"; } if (columna.VALUE == "Cantidad") { worksheet.Column(i).Style.NumberFormat.Format = "##.######"; } i++; } worksheet.Row(1).Style.Protection.SetLocked(true); MemoryStream s = (MemoryStream)GetStream(workbook); HttpResponse response = HttpContext.Current.Response; response.ClearContent(); response.Clear(); response.ContentType = "text/plain"; response.AddHeader("Content-Disposition", "attachment; filename=sale_order.xlsx;"); //response.TransmitFile(HttpContext.Current.Server.MapPath("~/UploadedFiles/SalesOrders/sale_order.xlsx")); response.BinaryWrite(s.ToArray()); response.Flush(); response.End(); }
//System.Data.DataSet ds, string[] sheetName, string fileName, List<DropdownModelExcel> list,int[] DateArry public static void ExportMultiSheetExcelExtend(ExcelPara Para) { try { using (XLWorkbook wb = new XLWorkbook()) { for (int i = 0; i < Para.sheetName.Length; i++) { if (Para.ds.Tables[i].Rows.Count > 0) { Para.ds.Tables[i].TableName = Para.sheetName[i]; wb.Worksheets.Add(Para.ds.Tables[i]); } } IXLWorksheet worksheet = null;; if (Para.list != null) { foreach (DropdownModelExcel item in Para.list) { worksheet = wb.Worksheet(item.SheetShowDrop); if (item.IsHideSheetDataDrop) { wb.Worksheet(item.SheetDataDrop).Hide(); } var worksheet2 = wb.Worksheet(item.SheetDataDrop); worksheet.Column(item.ColumnShowDropData).SetDataValidation().List(worksheet2.Range(item.RangeSheetData), true); } } else { worksheet = wb.Worksheet(1); } if (Para.DateColumns.Length > 0) { for (int i = 0; i < Para.DateColumns.Length; i++) { worksheet.Column(Para.DateColumns[i]).Style.DateFormat.Format = Para.DateFormat; } } HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Charset = ""; HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + Para.fileName + ".xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } } } catch (Exception ex) { } }
public void WriteSummary(IXLWorksheet ws, Tuple <List <ProductReportModel>, string[]> summaryData) { List <ProductReportModel> productReportModel = summaryData.Item1; string[] productsArray = summaryData.Item2; var col1 = ws.Column(1); col1.Width = 15; var col2 = ws.Column(2); col2.AdjustToContents(); col2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; int writingRow = 1; for (int i = 0; i < productsArray.Length; i++) { decimal?totQuantita = productReportModel .Where(p => p.Descrizione.ToUpper().Contains(productsArray[i].ToUpper())) .Select(p => p.Quantita) .Sum(); decimal?prezzoUnitario = productReportModel .Where(p => p.Descrizione.ToUpper().Contains(productsArray[i].ToUpper())) .Select(p => p.PrezzoUnitario) .FirstOrDefault(); decimal?totPrezzoTotale = productReportModel .Where(p => p.Descrizione.ToUpper().Contains(productsArray[i].ToUpper())) .Select(p => p.PrezzoTotale) .Sum(); ws.Cell(writingRow, 1).RichText .AddText("Prodotto:") .SetBold(); ws.Cell(writingRow, 2).SetValue(productsArray[i]); ws.Cell(writingRow + 1, 1).RichText .AddText("Totale quantita:") .SetBold(); ws.Cell(writingRow + 1, 2).SetValue(totQuantita); ws.Cell(writingRow + 2, 1).RichText .AddText("Prezzo unitario:") .SetBold(); ws.Cell(writingRow + 2, 2).SetValue(prezzoUnitario); ws.Cell(writingRow + 3, 1).RichText .AddText("Totale:") .SetBold(); ws.Cell(writingRow + 3, 2).SetValue(totPrezzoTotale); writingRow += 6; } }
private int AddHeaderBankTransactionForeignCurrencyModel(IXLWorksheet worksheet) { var colIdx = AddHeaderBankTransactionModel(worksheet); worksheet.Column(colIdx).Style.NumberFormat.NumberFormatId = 2; worksheet.Cell(1, colIdx++).Value = "AmountForeignCurrency"; worksheet.Cell(1, colIdx++).Value = "ForeignCurrencyIso"; worksheet.Column(colIdx).Style.NumberFormat.NumberFormatId = 2; worksheet.Cell(1, colIdx++).Value = "ExchangeRate"; return(colIdx); }
/// <summary> /// Set an entire column to a specific format. By default Excel defines the /// cell style in the following order; cell, row, column, worksheet default /// </summary> /// <param name="col">Column to set the format for</param> /// <param name="numberFormat">Optional number formatting string for the cell</param> /// <param name="dateFormat">Optional DateTime formatting string for the cell</param> /// <param name="fontStyle">Optional font style for the cell</param> /// <param name="fontSize">Optional font size for the cell</param> /// <param name="fontName">Optional font name for the cell</param> public void SetColumnFormat( int col, string numberFormat = null, string dateFormat = null, FontStyle?fontStyle = null, float?fontSize = null, string fontName = null) { var xlColumn = _sheet.Column(col + 1); UpdateStyle(xlColumn.Style, numberFormat, dateFormat, fontStyle, fontSize, fontName); }
private void FillSampleData(IXLWorksheet ws) { ws.Column(1).Style.Alignment.SetWrapText(true); ws.Column(1).Width = 30; ws.Column(2).Width = 30; ws.Range("C1:P1").Cells() .ForEach(c => c.Value = new DateTime(2016, 1, 1).AddDays(c.Address.ColumnNumber * 7)); ws.Range("C2:P19").Cells() .ForEach(c => c.Value = Math.Round(c.Address.RowNumber * Math.Sin(c.Address.ColumnNumber) * 10, 0)); }
private static void FillSheet(IXLWorksheet ws, AttributeGroupsOfObjects[] dataSet) { ws.Column(1).Width = 34; ws.Column(2).Width = 34; var lastRow = 1; foreach (var group in dataSet) { ws.Cell(lastRow, 1).Style.Font.SetBold(); ws.Cell(lastRow, 1).SetValue(lastRow); lastRow++; } }
private static void FitToColumns(IXLWorksheet ws, int count) { //wrap text ws.Column(2).Width = 20; ws.Column(3).Width = 30; for (var i = 0; i < count; i++) { ws.Cell(4 + i, 2).Style.Alignment.WrapText = true; ws.Cell(4 + i, 2).Style.Alignment.ShrinkToFit = true; ws.Cell(4 + i, 3).Style.Alignment.WrapText = true; ws.Cell(4 + i, 3).Style.Alignment.ShrinkToFit = true; } }
private void AdjustColumns(CremaDataTable dataTable, IXLWorksheet worksheet) { var index = 1; if (this.settings.OmitAttribute == false) { worksheet.Column(index++).AdjustToContents(); worksheet.Column(index++).AdjustToContents(); } foreach (var item in this.GetSortedColumn(dataTable)) { var column = worksheet.Column(index++); column.AdjustToContents(); } if (dataTable.Parent != null) { var column = worksheet.Column(index++); column.AdjustToContents(); } if (this.settings.OmitSignatureDate == false) { worksheet.Column(index++).AdjustToContents(); worksheet.Column(index++).AdjustToContents(); worksheet.Column(index++).AdjustToContents(); worksheet.Column(index++).AdjustToContents(); } }
private int AddHeaderBankTransactionModel(IXLWorksheet worksheet) { var colIdx = 1; worksheet.Column(colIdx).Style.NumberFormat.NumberFormatId = 0; worksheet.Cell(1, colIdx++).Value = "AccountId"; worksheet.Cell(1, colIdx++).Value = "AvailabilityDate"; worksheet.Cell(1, colIdx++).Value = "PostingDate"; worksheet.Cell(1, colIdx++).Value = "Text"; worksheet.Column(colIdx).Style.NumberFormat.NumberFormatId = 44; worksheet.Cell(1, colIdx++).Value = "Amount"; worksheet.Cell(1, colIdx++).Value = "CurrencyIso"; return(colIdx); }
public static void StyleTypes(this IXLWorksheet worksheet, PropertyInfo[] columnTypes, int beginningDataRow, int lastDataRow) { for (int i = 0; i < columnTypes.Count(); i++) { if (columnTypes[i].PropertyType == typeof(TimeSpan) || columnTypes[i].PropertyType == typeof(TimeSpan?)) { worksheet.Column(i + 1).Cells(beginningDataRow + 1, lastDataRow).Style.NumberFormat.NumberFormatId = 20; // H:mm } else if (columnTypes[i].PropertyType == typeof(bool)) { worksheet.Column(i + 1).CellsUsed(ce => ce.Value as bool? == true).Value = "Ja"; worksheet.Column(i + 1).CellsUsed(ce => ce.Value as bool? == false).Value = "Nej"; } } }
public override void InsertNewColumnBeforeFirst(string header) { var newWorkbook = new XLWorkbook(); var newWorksheet = newWorkbook.Worksheets.Add(worksheet.Name); newWorksheet.Cell(StartRow, StartColumn).Value = header; for (var row = StartRow; row <= CountRows(); row++) { for (var column = StartColumn; column <= CountColumns(); column++) { newWorksheet.Cell(row, column + 1).Value = worksheet.Cell(row, column); } } for (var column = StartColumn; column <= CountColumns(); column++) { newWorksheet.Column(column + 1).Width = worksheet.Column(column).Width; } worksheet.Delete(); workbook.Dispose(); worksheet = newWorksheet; workbook = newWorkbook; newWorksheet.Column(StartColumn).SetAutoFilter(); newWorksheet.Column(StartColumn).Width = header.Length + 2; newWorksheet.Range(StartRow, StartColumn, CountRows(), CountColumns()).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); }
/// <summary> /// Apply formatting to the worksheet /// </summary> /// <param name="worksheet">The worksheet to format</param> private void FormatWorksheet(ref IXLWorksheet worksheet) { worksheet.ColumnsUsed().AdjustToContents(); worksheet.SheetView.FreezeRows(1); worksheet.Rows().Height = 30; worksheet.Column(3).Width = 40; }
public static string DownloadExcel(DataTable dt, string fileName) { using (XLWorkbook xLWorkbook = new XLWorkbook()) { xLWorkbook.Worksheets.Add(dt, fileName); IXLWorksheet workSheet = xLWorkbook.Worksheet(1); workSheet.TabColor = XLColor.Black; workSheet.RowHeight = 12.0; workSheet.Protection.Protected = true; for (int col = 1; col <= 3; ++col) { workSheet.Column(col).Style.Protection.Locked = false; } string excelFileName = fileName + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; string path = Path.Combine(Path.GetTempPath(), excelFileName); using (MemoryStream memoryStream = new MemoryStream()) { xLWorkbook.SaveAs(memoryStream); using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write)) { memoryStream.WriteTo(fileStream); fileStream.Close(); } } return(excelFileName); } }
private static void AddFooterToWorksheet(IXLWorksheet worksheet, int actualRowNumber) { var actualLetter = 'B'; actualLetter = GetNextLetter(actualLetter); worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value = "Instructores"; var rngInst = worksheet.Range(actualLetter.ToString() + actualRowNumber); rngInst.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; rngInst.Style.Font.Bold = true; rngInst.Style.Font.Underline = XLFontUnderlineValues.Single; rngInst.Style.Font.FontSize = 28; rngInst.Style.Font.FontName = "Calibri"; actualLetter = GetNextLetter(actualLetter); worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value = "Sciancalepore, Juan C."; worksheet.Cell(actualLetter.ToString() + (actualRowNumber + 1)).Value = "Zechillo, Pablo"; var rngNames = worksheet.Range(actualLetter.ToString() + actualRowNumber + ":" + actualLetter.ToString() + (actualRowNumber + 1)); rngNames.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; rngNames.Style.Font.Bold = true; rngNames.Style.Font.FontSize = 26; rngNames.Style.Font.FontName = "Calibri"; worksheet.Column(1).AdjustToContents(11, 12); worksheet.Columns(2, 7).AdjustToContents(); }
public void GetCellText(IXLWorksheet RowsHead, double nWidthColumn, string StringCell, string NumCell, string sValue, XLAlignmentHorizontalValues Align, string index, string sCC) { //RowsHead.Range(StringCell + NumCell+":") var temColumn = RowsHead.Column(StringCell); //temColumn.Style.Fill.BackgroundColor = XLColor.DarkOrange; temColumn.Width = nWidthColumn; var itemCell = RowsHead.Cell(StringCell + NumCell); itemCell.Value = sValue; itemCell.Style.Alignment.Horizontal = Align; itemCell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; //itemCell.Style.Font.Bold = true; itemCell.Style.Font.FontName = "Cordia New"; itemCell.Style.Font.FontSize = 12; //itemCell.Style.Font.Underline = true; itemCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin; itemCell.Style.Border.RightBorder = XLBorderStyleValues.Thin; itemCell.Style.Border.TopBorder = XLBorderStyleValues.Thin; itemCell.Style.Border.BottomBorder = XLBorderStyleValues.Thin; itemCell.Style.Fill.BackgroundColor = XLColor.FromHtml(sCC); itemCell.Style.Border.LeftBorderColor = XLColor.Black; itemCell.Style.Border.RightBorderColor = XLColor.Black; itemCell.Style.Border.TopBorderColor = XLColor.Black; itemCell.Style.Border.BottomBorderColor = XLColor.Black; itemCell.Style.Alignment.WrapText = true; }
public void getexceList() //заполняет модель сигнала и создает лист сигналов. { int KKScolumn = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("KKS")).First().WorksheetColumn().ColumnNumber(); int typecolumn1 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Inp_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn; int typecolumn2 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Inp2_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn; int typecolumn3 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Out_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn; int typecolumn4 = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("T_Out2_Type_M")).First().WorksheetColumn().ColumnNumber() - KKScolumn; int cabpos = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("KKS кабеля")).First().WorksheetColumn().ColumnNumber() - KKScolumn; int conpos = _sheet.FirstRowUsed().Cells(a => a.GetString().Contains("Connection")).First().WorksheetColumn().ColumnNumber() - KKScolumn; signals = new List <Signalinexcel>(); var rngs = _sheet.Range(_sheet.Column(KKScolumn).FirstCellUsed().CellBelow(), _sheet.Column(KKScolumn).LastCellUsed()); rngs.Cells().ForEach(a => { if (a.GetString() != "") { var r1 = _sheet.Range(a.CellRight(cabpos).Address, a.CellRight(cabpos + 3).Address);//берет только первый кабель, исправить. var r2 = _sheet.Range(a.CellRight(cabpos + 5).Address, a.CellRight(cabpos + 8).Address); string zlk = (a.CellRight(cabpos + 4).GetString().Trim(',') + "," + a.CellRight(cabpos + 9).GetString()).Trim(','); string cab = r1.Cells().Aggregate("", (str, c) => str.Trim('#') + "#" + c.GetString()) + r2.Cells().Aggregate("", (str, c) => str.Trim('#') + "#" + c.GetString()); signals.Add(new Signalinexcel(a.GetString().Trim(), a.CellRight(1).GetString().Trim(), a.CellRight(typecolumn1).GetString().Trim(), a.CellRight(typecolumn2).GetString().Trim(), a.CellRight(typecolumn3).GetString().Trim(), a.CellRight(typecolumn4).GetString().Trim(), cab.Trim(), a.CellRight(conpos).GetString().Trim(), zlk)); } }); }
private static bool SplitKD(IXLWorksheet worksheet, string title, int column) { // row to scan for KD const int row = 6; try { while (!worksheet.Cell(row, column).IsEmpty()) { // scan for first KD derivative in TRIM LEVEL row if (worksheet.Cell(row, column).GetString().EndsWith("KD")) { // remove existing column grouping - commented out as grouping no longer applied as you can't un-group on a protected worksheet // worksheet.Columns(8, worksheet.LastColumnUsed().ColumnNumber()).Ungroup(); // add KD SPEC GROUP heading worksheet.Cell(1, column).Value = title.ToUpper() + " KD"; // insert and size new divider column before KD derivatives worksheet.Column(column).InsertColumnsBefore(1); worksheet.Column(column).Width = 8; // group non-KD derivatives - commented out as you can't un-group on a protected worksheet // worksheet.Columns(8, column - 1).Group(); // merge non-KD SPEC GROUP heading worksheet.Range(1, 8, 1, column - 1).Merge(); // group KD derivatives - commented out as you can't un-group on a protected worksheet // worksheet.Columns(column + 1, worksheet.LastColumnUsed().ColumnNumber()).Group(); // merge KD SPEC GROUP heading worksheet.Range(1, column + 1, 1, worksheet.LastColumnUsed().ColumnNumber()).Merge(); // add vertical KD title worksheet.Cell(1, column).Value = title.ToUpper() + " KD"; // merge and format vertical divider heading worksheet.Range(1, column, 9, column).Merge(); worksheet.Range(1, column, worksheet.LastRowUsed().RowNumber(), column).Style .Font.SetBold(true) .Font.SetFontColor(XLColor.White) .Fill.SetBackgroundColor(XLColor.Black) .Alignment.SetVertical(XLAlignmentVerticalValues.Bottom) .Alignment.SetTextRotation(90); // do for first KD derivative then break out return true; } column = column + 1; } return false; } catch (Exception ex) { Log.Error(ex); throw; } }
private static void HideNoDerivativeColumn(IXLWorksheet worksheet, int column) { const int row = 9; try { while (!worksheet.Cell(row, column).IsEmpty() || worksheet.Cell(row, column).Style.Fill.BackgroundColor == XLColor.Black) { if (worksheet.Cell(row, column).GetString() == "NO") { worksheet.Column(column).Hide(); } column = column + 1; } } catch (Exception ex) { Log.Error(ex); throw; } }
private void AddHeader(VSProject project, IXLWorksheet worksheet, IEnumerable<CultureInfo> cultures) { worksheet.Cell(1, 1).Value = "ID"; worksheet.Cell(1, 2).Value = "Keys"; int c = 3; foreach (var culture in cultures) { if (AutoAdjustLayout) { worksheet.Column(c).Width = ColumnValueWidth; worksheet.Column(c).Style.Alignment.SetWrapText(true); } worksheet.Cell(1, c++).Value = culture.Name; if (ExportComments) { if (AutoAdjustLayout) { worksheet.Column(c).Width = ColumnCommentWidth; worksheet.Column(c).Style.Alignment.SetWrapText(true); } worksheet.Cell(1, c++).Value = culture.Name + " [Comments]"; } } }
private void createMinorIntervalWorkheet(IXLWorksheet worksheet) { worksheet.Column("A").Width = 30; worksheet.Column("B").Width = 30; worksheet.Column("C").Width = 30; worksheet.Cell("A1").Value = "DATE"; addRightBottomBorder(worksheet.Cell("A1")); worksheet.Cell("B1").Value = "TIME"; addRightBottomBorder(worksheet.Cell("B1")); worksheet.Cell("C1").Value = "dB LAeq, xx min"; addBottomBorder(worksheet.Cell("C1")); int index = 1; foreach (var r in readings.Where(x=>!x.Major).OrderBy(x=>x.Time)) { index++; worksheet.Cell("A" + index).Value = r.Time.ToString("dd/MM/yy"); addRightBottomBorder(worksheet.Cell("A" + index)); worksheet.Cell("B" + index).Value = r.Time.ToString("HH:mm"); addRightBottomBorder(worksheet.Cell("B" + index)); worksheet.Cell("C" + index).Value = Math.Round(r.Data.LAeq, 1).ToString(); addBottomBorder(worksheet.Cell("C" + index)); } }
private void createAllDataWorkheet(IXLWorksheet worksheet) { worksheet.Column("A").Width = 30; worksheet.Column("B").Width = 30; worksheet.Column("C").Width = 30; worksheet.Cell(1, 1).Value = "DATE"; addRightBottomBorder(worksheet.Cell(1, 1)); worksheet.Cell(1, 2).Value = "TIME"; addRightBottomBorder(worksheet.Cell(1, 2)); worksheet.Cell(1, 3).Value = "LAeq"; addRightBottomBorder(worksheet.Cell(1, 3)); worksheet.Cell(1, 4).Value = "LAMax"; addRightBottomBorder(worksheet.Cell(1, 4)); worksheet.Cell(1, 5).Value = "LAMin"; addRightBottomBorder(worksheet.Cell(1, 5)); worksheet.Cell(1, 6).Value = "LZMax"; addRightBottomBorder(worksheet.Cell(1, 6)); worksheet.Cell(1, 7).Value = "LZMin"; addRightBottomBorder(worksheet.Cell(1, 7)); int col = 8; Type oneThird = typeof(ReadingData.OctaveBandOneThird); foreach (var propertyInfo in oneThird.GetProperties()) { worksheet.Cell(1, col).Value = "1/3 " + propertyInfo.Name.Replace("_", ".").Replace("Hz", "") + " Hz"; addRightBottomBorder(worksheet.Cell(1, col)); col++; } Type oneOne = typeof(ReadingData.OctaveBandOneOne); foreach (var propertyInfo in oneOne.GetProperties()) { worksheet.Cell(1, col).Value = "1/1 " + propertyInfo.Name.Replace("_", ".").Replace("Hz", "") + " Hz"; addRightBottomBorder(worksheet.Cell(1, col)); col++; } int index = 2; foreach (var r in readings.OrderBy(x => x.Time)) { worksheet.Cell(index, 1).Value = r.Time.ToString("dd/MM/yyyy"); addRightBottomBorder(worksheet.Cell(index, 1)); worksheet.Cell(index, 2).Value = r.Time.ToString("HH:mm"); addRightBottomBorder(worksheet.Cell(index, 2)); worksheet.Cell(index, 3).Value = oneDig(r.Data.LAeq); addRightBottomBorder(worksheet.Cell(index, 3)); worksheet.Cell(index, 4).Value = oneDig(r.Data.LAMax); addRightBottomBorder(worksheet.Cell(index, 4)); worksheet.Cell(index, 5).Value = oneDig(r.Data.LAMin); addRightBottomBorder(worksheet.Cell(index, 5)); worksheet.Cell(index, 6).Value = oneDig(r.Data.LZMax); addRightBottomBorder(worksheet.Cell(index, 6)); worksheet.Cell(index, 7).Value = oneDig(r.Data.LZMin); addRightBottomBorder(worksheet.Cell(index, 7)); col = 8; foreach (var propertyInfo in oneThird.GetProperties()) { worksheet.Cell(index, col).Value = oneDig((Double)propertyInfo.GetValue(r.Data.LAeqOctaveBandOneThird)); addRightBottomBorder(worksheet.Cell(index, col)); col++; } foreach (var propertyInfo in oneOne.GetProperties()) { worksheet.Cell(index, col).Value = oneDig((Double)propertyInfo.GetValue(r.Data.LAeqOctaveBandOneOne)); addRightBottomBorder(worksheet.Cell(index, col)); col++; } index++; } }
private static void SetOxidesTable(IXLWorksheet ws) { var mainOxides = Model.OxidesResultModel.First().OxidesResult.Select(x => x.Oxide.Formula); var oxides = Model.Oxides.Where(x => !mainOxides.Contains(x.Composition.Formula)); var countInsert = oxides.Count() - 3; if (countInsert > 0) { ws.Column("AV").InsertColumnsAfter(oxides.Count() - 3);//44 - 51 template column ws.Range(5, 44, 8, 51 + countInsert).Merge(); ws.Range(18, 44, 18, 50 + countInsert).Merge(); } var currentCol = 48; foreach (var oxide in oxides) { var currentCell = ws.Cell(9, currentCol); currentCell.Style.Border.RightBorder = XLBorderStyleValues.Thin; currentCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin; SetChemicalFormula(currentCell.RichText, oxide.Composition.Formula); var currentResCell = ws.Cell(19, currentCol); SetChemicalFormula(currentResCell.RichText, oxide.Composition.Formula); currentCol++; } }
// 构造表格标题和标题行 int BuildTextPageTop(PrintOption option, Hashtable macro_table, StreamWriter sw, // ref ExcelDocument doc IXLWorksheet sheet ) { // 表格标题 string strTableTitleText = option.TableTitle; if (String.IsNullOrEmpty(strTableTitleText) == false) { strTableTitleText = StringUtil.MacroString(macro_table, strTableTitleText); if (sw != null) { sw.WriteLine(strTableTitleText); sw.WriteLine(""); } if (sheet != null) { #if NO doc.WriteExcelTitle(0, option.Columns.Count, // nTitleCols, strTableTitleText, 6); #endif var header = sheet.Range(1, 1, 1, option.Columns.Count).Merge(); header.Value = strTableTitleText; header.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // header.Style.Font.FontName = "微软雅黑"; header.Style.Font.Bold = true; header.Style.Font.FontSize = 16; } } string strColumnTitleLine = ""; List<int> widths = new List<int>(); for (int i = 0; i < option.Columns.Count; i++) { Column column = option.Columns[i]; widths.Add(column.WidthChars); string strCaption = column.Caption; // 如果没有caption定义,就挪用name定义 if (String.IsNullOrEmpty(strCaption) == true) strCaption = column.Name; // string strClass = Global.GetLeft(column.Name); if (i != 0) strColumnTitleLine += "\t"; strColumnTitleLine += strCaption; if (sheet != null) { #if NO doc.WriteExcelCell( 2, i, strCaption, true); #endif var cell = sheet.Cell(2+1, i+1); cell.Value = strCaption; // cell.Style.Font.FontName = "微软雅黑"; cell.Style.Font.Bold = true; if (column.WidthChars != -1) sheet.Column(i + 1).Width = column.WidthChars; } } if (sw != null) sw.WriteLine(strColumnTitleLine); #if NO if (doc != null) SetColumnWidth(doc, widths); #endif return 0; }
private void createCoverWorksheet(IXLWorksheet worksheet) { worksheet.Column("A").Width = 21; worksheet.Column("B").Width = 26; worksheet.Cell("A1").Value = "AUDIOVIEW EXPORT"; worksheet.Range("A1","B1").Merge(); addBottomBorder(worksheet.Cell("A1")); addBottomBorder(worksheet.Cell("B1")); worksheet.Cell("A2").Value = "PROJECT NAME"; addRightBottomBorder(worksheet.Cell("A2")); worksheet.Cell("B2").Value = project.Name; addBottomBorder(worksheet.Cell("B2")); worksheet.Cell("A3").Value = "PROJECT NUMBER"; addRightBottomBorder(worksheet.Cell("A3")); worksheet.Cell("B3").Value = project.Number; addBottomBorder(worksheet.Cell("B3")); worksheet.Cell("A4").Value = "DATE"; addRightBottomBorder(worksheet.Cell("A4")); worksheet.Cell("B4").Value = project.Created.ToString(); addBottomBorder(worksheet.Cell("B4")); worksheet.Cell("A5").Value = "MINOR INTERVAL PERIOD"; addRightBottomBorder(worksheet.Cell("A5")); worksheet.Cell("B5").Value = project.MinorInterval.ToString(); addBottomBorder(worksheet.Cell("B5")); worksheet.Cell("A6").Value = "MINOR INTERVAL LIMIT"; addRightBottomBorder(worksheet.Cell("A6")); worksheet.Cell("B6").Value = project.MinorDBLimit; addBottomBorder(worksheet.Cell("B6")); worksheet.Cell("A7").Value = "MAJOR INTERVAL PERIOD"; addRightBottomBorder(worksheet.Cell("A7")); worksheet.Cell("B7").Value = project.MajorInterval.ToString(); addBottomBorder(worksheet.Cell("B7")); worksheet.Cell("A8").Value = "MAJOR INTERVAL LIMIT"; addRightBottomBorder(worksheet.Cell("A8")); worksheet.Cell("B8").Value = project.MajorDBLimit; addBottomBorder(worksheet.Cell("B8")); }
private IXLCell ShowDataPerEntry(IXLWorksheet workSheet, StatistikRootClass.StatistikDataSelectionTypes writeOutType, StandardCounter Counter, int StartRowIndex, int StartColumnIndex, bool showZeros = true) { int ActuallRow = StartRowIndex; int ColumnIndex = StartColumnIndex; foreach (string writeOutSpecificHeadLine in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType).Keys) { workSheet.Column(ColumnIndex).Width = 1; workSheet.Cell(ActuallRow, ColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); foreach (string columnText in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine]) { ColumnIndex = SetOneValue(workSheet, ActuallRow, ColumnIndex, Counter.GetWriteOutSpecificValues (writeOutType)[writeOutSpecificHeadLine][columnText], showZeros); } } return workSheet.Cell(ActuallRow, ColumnIndex); }
public void ConfigureCell(IXLWorksheet worksheet, SurveyType surveyType) { #region SetUp int numberOfCells = 0; int detailColumns = 1; // Inicialized in one because the last column switch (surveyType) { case SurveyType.CareTeam: detailColumns += 5; numberOfCells = QuestionKey.CareTeamSurvey.NumberOfKeys() + detailColumns; break; case SurveyType.PCP: detailColumns += 7; numberOfCells = QuestionKey.PCPSurvey.NumberOfKeys() + detailColumns; break; case SurveyType.Specialist: detailColumns += 7; numberOfCells = QuestionKey.SpecialistSurvey.NumberOfKeys() + detailColumns; break; } #endregion #region Headers for (int x = 1; x <= numberOfCells; x++) { worksheet.Cell(1, x).Style.Font.Bold = true; worksheet.Cell(1, x).Style.Alignment.WrapText = true; worksheet.Cell(1, x).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; worksheet.Cell(1, x).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; worksheet.Cell(1, x).Style.Fill.BackgroundColor = XLColor.LightCyan; worksheet.Cell(1, x).Style.Font.FontSize = 11; worksheet.Cell(1, x).Style.Border.BottomBorder = XLBorderStyleValues.Thin; worksheet.Cell(1, x).Style.Border.LeftBorder = XLBorderStyleValues.Thin; worksheet.Cell(1, x).Style.Border.TopBorder = XLBorderStyleValues.Thin; worksheet.Cell(1, x).Style.Border.RightBorder = XLBorderStyleValues.Thin; if (x > detailColumns) { if (x == numberOfCells) worksheet.Column(x).Width = 55; else worksheet.Column(x).Width = 30; } else worksheet.Column(x).Width = 20; } #endregion #region Rows for (int x = 1; x <= numberOfCells; x++) { worksheet.Cell(2, x).Style.Alignment.WrapText = true; worksheet.Cell(2, x).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; worksheet.Cell(2, x).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; worksheet.Cell(2, x).Style.Font.FontSize = 11; worksheet.Cell(2, x).Style.Border.BottomBorder = XLBorderStyleValues.Thin; worksheet.Cell(2, x).Style.Border.LeftBorder = XLBorderStyleValues.Thin; worksheet.Cell(2, x).Style.Border.TopBorder = XLBorderStyleValues.Thin; worksheet.Cell(2, x).Style.Border.RightBorder = XLBorderStyleValues.Thin; } #endregion }
private int CreateXLSXHeadLines(IXLWorksheet workSheet, IXLCell StartTableCell, StatistikRootClass.StatistikDataSelectionTypes writeOutType) { workSheet.Column("A").Width = 2; int LineNumber = StartTableCell.WorksheetRow().RowNumber(); int StartColumnIndex = StartTableCell.WorksheetColumn().ColumnNumber(); // FullTable.Theme = XLTableTheme.TableStyleLight20; CreateGroupHeader(workSheet, "AG's, OrtsTeile, Orte", LineNumber, StartColumnIndex, 6); workSheet.Column(StartColumnIndex).Width = 3; workSheet.Column(StartColumnIndex + 1).Width = 3; workSheet.Column(StartColumnIndex + 2).Width = 3; int OldStartColumnIndex = StartColumnIndex; foreach (string LevelText in StandardCounter.AllHeadLines["LevelHeadLines"]) { if ((LevelText == "Gesamt") || (LevelText == "AddProcesses") || (LevelText == "Beschreibung") ) continue; CreateVerticalNumberHeader(workSheet, LevelText, LineNumber + 1, StartColumnIndex++); } workSheet.Column(OldStartColumnIndex + 3).Width = 20; workSheet.Column(StartColumnIndex).Width = 1; workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, "WMU Punkte", LineNumber, StartColumnIndex, 3); // StartColumnIndex = 8; foreach (string LevelText in StandardCounter.AllHeadLines["WMUHeadLines"]) { CreateVerticalNumberHeader(workSheet, LevelText.Replace("W", "Weiblich").Replace("M", "Männlich").Replace("U", "Unbekannt"), LineNumber + 1, StartColumnIndex++); } workSheet.Column(StartColumnIndex).Width = 1; workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, "Projekt Phasen", LineNumber, StartColumnIndex, 5); foreach (string LevelText in StandardCounter.AllHeadLines["ProjektPhasenHeadLines"]) { CreateVerticalNumberHeader(workSheet, LevelText, LineNumber + 1, StartColumnIndex++); } // StandardCounter.AllHeadLines IXLCell StartCell = workSheet.Cell(LineNumber, StartColumnIndex); int RunningColumn = StartCell.Address.ColumnNumber; int RunningRow = StartCell.Address.RowNumber; foreach (string writeOutSpecificHeadLine in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType).Keys) { workSheet.Column(RunningColumn).Width = 1; workSheet.Cell(RunningRow, RunningColumn++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, writeOutSpecificHeadLine, LineNumber, RunningColumn, StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine].Count); foreach (string ColumText in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine]) { CreateVerticalNumberHeader(workSheet, ColumText, RunningRow + 1, RunningColumn++); } } return 0; }
public void RemoveColumn(string sheetName, int colNum) { worksheet = workbook.Worksheet(sheetName); worksheet.Column(colNum).Delete(); Save(); }
private void CreateVerticalNumberHeader(IXLWorksheet workSheet, string HeadLineText, int LineNumber, int StartColumnIndex) { workSheet.Cell(LineNumber, StartColumnIndex).Style .Font.SetBold() .Font.SetFontSize(9) .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left) .Alignment.SetTextRotation(90); workSheet.Cell(LineNumber, StartColumnIndex).Style .Alignment.WrapText = true; workSheet.Cell(LineNumber, StartColumnIndex).Value = HeadLineText; workSheet.Row(LineNumber).Height = 75; workSheet.Column(StartColumnIndex).Width = 4; }
private static void SetSystemsPhasesHeader(IXLWorksheet ws) { var startColumnFirstSystemOrig = 6; var startColumnFirstSystemCalc = 12; var countPhases = 5; var startColumnSecondSystemOrig = 22; var startColumnSecondSystemCalc = 28; var firstSystemPhasesCount = Model.FirstSystem.Phases.Count; var secondSystemPhasesCount = Model.SecondSystem.Phases.Count; var countToInsert = secondSystemPhasesCount - countPhases; if (countToInsert > 0) { ws.Column(startColumnSecondSystemCalc).InsertColumnsAfter(countToInsert); ws.Range(7, startColumnSecondSystemCalc, 8, startColumnSecondSystemCalc + countToInsert + countPhases - 1).Merge(); ws.Column(startColumnSecondSystemOrig).InsertColumnsAfter(countToInsert); ws.Range(7, startColumnSecondSystemOrig, 8, startColumnSecondSystemOrig + countToInsert + countPhases - 1).Merge(); ws.Range(5, 20, 6, 35 + countToInsert + countToInsert).Merge(); } countToInsert = firstSystemPhasesCount - countPhases; if (countToInsert > 0) { ws.Column(startColumnFirstSystemCalc).InsertColumnsAfter(countToInsert); ws.Range(7, startColumnFirstSystemCalc, 8, startColumnFirstSystemCalc + countToInsert + countPhases - 1).Merge(); ws.Column(startColumnFirstSystemOrig).InsertColumnsAfter(countToInsert); ws.Range(7, startColumnFirstSystemOrig, 8, startColumnFirstSystemOrig + countToInsert + countPhases - 1).Merge(); ws.Range(5, 2, 6, 19 + countToInsert + countToInsert).Merge(); } var firstSystemPhases = Model.FirstSystem.Phases.Select(x => x.Formula); var secondSystemPhases = Model.SecondSystem.Phases.Select(x => x.Formula); var currentCol = 6; foreach (var phase in firstSystemPhases) { SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase); currentCol++; } currentCol++; foreach (var phase in firstSystemPhases) { SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase); currentCol++; } currentCol += 5; foreach (var phase in secondSystemPhases) { SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase); currentCol++; } currentCol++; foreach (var phase in secondSystemPhases) { SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase); currentCol++; } }
public int WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet, StatistikRootClass.DistributionCountTyp DistTyp) { IXLRange headerRng = workSheet.Range("B2:F2").Merge(); headerRng.Style .Font.SetBold() .Fill.SetBackgroundColor(XLColor.AliceBlue) .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; headerRng.Value = "Vorschläge / Ideen Anzahlen " + DistTyp.GetDescription(); switch (DistTyp) { case StatistikRootClass.DistributionCountTyp.AllTogether: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; case StatistikRootClass.DistributionCountTyp.PerAG: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; case StatistikRootClass.DistributionCountTyp.PerOrtsTeil: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; default: return 7; } }