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