private static void GenerateMergeCells(XlWorksheet ws)
{
// объединение ячеек для заголовка
ws.AppendMergeCell(
new String[] { "A1:C1", "A2:C2", "A3:C3" }
);
}
private static void AppendValueToSpreadsheetCell(XlWorksheet ws, UInt32 rowIndex, UInt32 columnIndex, Object value, Type type)
{
String cellValueText = null;
if (value != null && value != DBNull.Value)
{
switch (type.ToString())
{
case "System.Decimal":
ws.UpsertCell(rowIndex, columnIndex, 3, value);
break;
case "System.Double":
ws.UpsertCell(rowIndex, columnIndex, 3, value);
break;
case "System.Int32":
ws.UpsertCell(rowIndex, columnIndex, 4, value);
break;
default:
if (value != null)
{
cellValueText = value.ToString();
}
ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText);
break;
}
}
else
{
ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText);
}
}
private static void GenerateBackgroundColor(XlWorksheet ws, DataTable dt)
{
for (Int32 ri = 0; ri < dt.Rows.Count; ri++)
{
DataRow dr = dt.Rows[ri];
if ((String)dr[4] == "v")
{
for (int ci = 0; ci < dt.Columns.Count; ci++)
{
ws.SetCellBackgroundColor(ri + 1, ci, "ffcccc"); // + 1 - кол-во строк в заголовке
}
}
}
}
private static void GenerateColumns(XlWorksheet ws, DataTable dt)
{
uint cn = 1;
foreach (DataColumn column in dt.Columns)
{
if (column.ExtendedProperties.ContainsKey("Width"))
{
Double width = (Double)column.ExtendedProperties["Width"];
ws.AppendColumn(cn, cn, false, true, width);
}
else
{
ws.AppendColumn(cn, cn, true);
}
cn++;
}
}
public XlWorksheet[] Wss; // массив экземпляров WS для заполненых worksheet с доп процедурами
public Spreadsheet(Int32 sheetCount = 1)
{
memoryStream = new System.IO.MemoryStream();
document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);
workbookPart1 = document.AddWorkbookPart();
workbook = new Workbook();
workbookPart1.Workbook = workbook;
workbookStylesPart = workbookPart1.AddNewPart <WorkbookStylesPart>();
stylesheet = (new StylesPart()).GenerateWorkbookStylesPartContent();
workbookStylesPart.Stylesheet = stylesheet;
sharedStringTablePart = workbookPart1.AddNewPart <SharedStringTablePart>();
sharedStringTable = (new SstPart()).GenerateSharedStringTablePart();
sharedStringTablePart.SharedStringTable = sharedStringTable;
sheets = new Sheets();
workbook.Append(sheets);
worksheetParts = new WorksheetPart[sheetCount];
Wss = new XlWorksheet[sheetCount];
for (UInt32 i = 0; i < sheetCount; i++)
{
// создаём новый лист со всеми частями (SheetViews, Columns, SheetData, MergeCells) (пока пустыми)
Wss[i] = new XlWorksheet(stylesheet, sharedStringTable);
worksheet = Wss[i].GetWorksheet();
// создаём новый WorksheetPart в workbookPart
worksheetParts[i] = workbookPart1.AddNewPart <WorksheetPart>();
// и добавляем новый лист в его корневой элемент Worksheet
worksheetParts[i].Worksheet = worksheet;
// ссылка на новый WorksheetPart
String rId = workbookPart1.GetIdOfPart(worksheetParts[i]);
// запись о новом листе в workbook
sheet = new Sheet()
{
Name = "Sheet" + (i + 1).ToString(), SheetId = (i + 1), Id = rId
};
sheets.Append(sheet);
}
}
private static void GeneratePageSetup(XlWorksheet ws)
{
ws.SetPageOrientationLandscape();
ws.SetPagePaperSizeA4();
ws.SetPageFitTo(width: 1);
}
private static void GenerateConditionalFormatting(XlWorksheet ws, UInt32 dxfId, DataTable dt)
{
ws.AppendConditionalFormatting(new String[] { "K3:K" + (dt.Rows.Count + 2).ToString() }, dxfId, "Value(E3)<>K3");
ws.AppendConditionalFormatting(new String[] { "J3:J" + (dt.Rows.Count + 2).ToString() }, dxfId, "D3<>J3");
}
public static void GenrateSheetData0(XlWorksheet ws, DataTable dt)
{
UInt32 rowIndex = 0;
UInt32 columnIndex = 0;
// строка заголовка
columnIndex = 0;
foreach (DataColumn column in dt.Columns)
{
String cellValueText = column.Caption;
ws.UpsertCell(rowIndex, columnIndex, 1, cellValueText); // CellValues.SharedString
columnIndex++;
}
rowIndex++;
// строки данных
for (Int32 ri = 0; ri < dt.Rows.Count; ri++)
{
columnIndex = 0;
foreach (DataColumn column in dt.Columns)
{
Object value = dt.Rows[ri][column.ColumnName];
Type type = column.DataType;
AppendValueToSpreadsheetCell(ws, rowIndex, columnIndex, value, type);
columnIndex++;
}
rowIndex++;
}
/*
* // сумма по таблице столбца "Сумма закуп."
* {
* String f = "=SUM(N2:N" + rowIndex.ToString() + ")";
* ws.UpsertCell(rowIndex, 13, 3, "0", f); // CellValues.Number
* }
* // сумма по таблице столбца "Вес"
* {
* String f = "=SUM(Q2:Q" + rowIndex.ToString() + ")";
* ws.UpsertCell(rowIndex, 16, 3, "0", f); // CellValues.Number
* }
* // сумма по таблице столбца "Объём"
* {
* String f = "=SUM(R2:R" + rowIndex.ToString() + ")";
* ws.UpsertCell(rowIndex, 17, 3, "0", f); // CellValues.Number
* }
* // сумма по таблице столбца "Предельная оптовая сумма"
* {
* String f = "=SUM(T2:T" + rowIndex.ToString() + ")";
* ws.UpsertCell(rowIndex, 19, 3, "0", f); // CellValues.Number
* }
* // сумма по таблице столбца "Сумма продажи"
* {
* String f = "=SUM(V2:V" + rowIndex.ToString() + ")";
* ws.UpsertCell(rowIndex, 21, 3, "0", f); // CellValues.Number
* }
* rowIndex++;
* // сумма по таблице столбца "Сумма закуп." если "Страна" == Россия
* {
* ws.UpsertCell(rowIndex, 12, 2, "Россия"); // CellValues.SharedString
* String f = String.Format(
* "=SUMIF(I{0}:I{1},\"=Россия\",N{0}:N{1})" +
* "+SUMIF(I{0}:I{1},\"=Республика Беларусь\",N{0}:N{1})" +
* "+SUMIF(I{0}:I{1},\"=Беларусь\",N{0}:N{1})" +
* "+SUMIF(I{0}:I{1},\"=Казахстан\",N{0}:N{1})" +
* "+SUMIF(I{0}:I{1},\"=Армения\",N{0}:N{1})", 2, (rowIndex - 1));
* ws.UpsertCell(rowIndex, 13, 3, "0", f); // CellValues.Number
* f = "=(N" + (rowIndex + 1).ToString() + "/N" + rowIndex.ToString() + ")*100";
* ws.UpsertCell(rowIndex, 14, 3, "0", f); // CellValues.Number
* }
* rowIndex++;
* // две таблицы с итогами и данными из шапки
* {
* ws.UpsertCell(rowIndex, 1, 2, "НМЦК"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 2, 3, h.Rows[0]["сумма_лота"]); // CellValues.Number
* ws.UpsertCell(rowIndex, 7, 2, "Сумма по закупке (руб)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 0D, "=N" + (rowIndex - 1).ToString()); // CellValues.Number
* rowIndex++;
* ws.UpsertCell(rowIndex, 1, 2, "График поставки"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 2, 2, h.Rows[0]["график_поставки"] as String); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 7, 2, "Наценка (%)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 10D); // CellValues.Number
* rowIndex++;
* ws.UpsertCell(rowIndex, 1, 2, "Срок годности"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 2, 2, h.Rows[0]["требования_по_сроку_годности"] as String); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 7, 2, "Прибыль (руб)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 0D, "=I" + (rowIndex - 1).ToString() + "*I" + rowIndex.ToString() + "/100"); // CellValues.Number
* rowIndex++;
* ws.UpsertCell(rowIndex, 7, 2, "Сумма с наценкой (руб)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 0D, "=I" + (rowIndex - 2).ToString() + "+I" + rowIndex.ToString()); // CellValues.Number
* rowIndex++;
* ws.UpsertCell(rowIndex, 7, 2, "Транспорт (руб)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 0D); // CellValues.Number
* rowIndex++;
* ws.UpsertCell(rowIndex, 7, 2, "Минимальная сумма (руб)"); // CellValues.SharedString
* ws.UpsertCell(rowIndex, 8, 3, 0D, "=I" + (rowIndex - 1).ToString() + "+I" + rowIndex.ToString()); // CellValues.Number
* rowIndex++;
* }
*/
}