/// <summary>
/// Sets up styles for workbook
/// </summary>
/// <param name="workbook"></param>
public static void Apply(Workbook workbook)
{
var s0 = new Style(TitleStyle)
{
Font =
{
Size = 16,
Bold = true,
Color = "#000000"
},
Alignment = new Alignment
{
Horizontal = Horizontal.Center
}
};
workbook.AddStyle(s0);
// create style s1 for header
var s1 = new Style(SummaryStyle)
{
Font =
{
Bold = true,
Color = "#000000"
},
Borders = GetBorders(),
Interior = new Interior()
{
Color = "#cccccc",
Pattern = Pattern.Solid
}
};
workbook.AddStyle(s1);
// create style s2 for header
var s2 = new Style(Headerstyle)
{
Font =
{
Bold = true,
Size = 12
},
Borders = GetBorders(),
Interior = new Interior()
{
Color = "#dedede",
Pattern = Pattern.Solid
}
};
workbook.AddStyle(s2);
}
private void CreateStyles(Workbook workbook)
{
var style1 = workbook.AddStyle("id1");
style1.InteriorStyle.Pattern = InteriorStylePattern.Solid;
style1.InteriorStyle.Color = "#ededed";
var style2 = workbook.AddStyle("id2");
style2.Borders.Add(BorderStylePosition.Bottom, "black", BorderLineStyle.Continuous);
style2.Borders.Add(BorderStylePosition.Left, "black", BorderLineStyle.Continuous);
style2.Borders.Add(BorderStylePosition.Right, "black", BorderLineStyle.Continuous);
style2.Borders.Add(BorderStylePosition.Top, "black", BorderLineStyle.Continuous);
style2.FontStyle.Bold = true;
style2.FontStyle.Italic = true;
style2.AlignmentStyle.Horizontal = HorizontalAlignment.Right;
}
public void Workbook_AddStyle_ByName()
{
// Arrange
var book = new Workbook();
// Act
var style = book.AddStyle("id");
// Assert
Assert.AreEqual(1, book.Styles.Count);
}
public void Workbook_ClearStyle()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
// Act
book.ClearStyles();
// Assert
Assert.AreEqual(0, book.Styles.Count);
}
public void Workbook_GetStyle()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
// Act
var returnedStyle = book.GetStyle("id");
// Assert
Assert.AreEqual(style, returnedStyle);
}
public void Workbook_RemoveStyle_ByName_WrongId_ReturnsFalse()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
// Act
var value = book.RemoveStyle("wrongid");
// Assert
Assert.IsFalse(value);
Assert.AreEqual(1, book.Styles.Count);
}
public void Workbook_RemoveStyle_ById()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
// Act
var value = book.RemoveStyle("id");
// Assert
Assert.IsTrue(value);
Assert.AreEqual(0, book.Styles.Count);
}
public void Workbook_TryGetStyle_WrongId_ReturnsFalse()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
Style returnedStyle;
// Act
var value = book.TryGetStyle("wrongid", out returnedStyle);
// Assert
Assert.IsFalse(value);
Assert.IsNull(returnedStyle);
}
public void Workbook_TryGetStyle()
{
// Arrange
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
Style returnedStyle;
// Act
var value = book.TryGetStyle("id", out returnedStyle);
// Assert
Assert.IsTrue(value);
Assert.AreEqual(style, returnedStyle);
}
public void Workbook_GetStyle_WrongId_ThrowsException()
{
// Arrange
var thrown = false;
var style = new Style("id");
var book = new Workbook();
book.AddStyle(style);
// Act
try
{
var returnedStyle = book.GetStyle("wrongid");
}
catch { thrown = true; }
// Assert
Assert.IsTrue(thrown);
}
public ExcelResult Export()
{
string result = string.Empty;
Workbook wb = new Workbook();
// properties
wb.Properties.Author = "Grapart";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "Grapart";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// create style s1 for header
Style s1 = new Style("s1");
s1.Font.Bold = true;
s1.Font.Italic = true;
s1.Font.Color = "#21610B";
wb.AddStyle(s1);
// create style s2 for header
Style s2 = new Style("s2");
s2.Font.Bold = true;
s2.Font.Italic = true;
s2.Borders.Add(new Border());
s2.Font.Color = "#0000FF";
wb.AddStyle(s2);
//s3
Style s3 = new Style("s3");
s3.Font.Bold = true;
s3.Font.Italic = true;
s3.Borders.Add(new Border());
s3.Font.Color = "#FF0000";
wb.AddStyle(s3);
// First sheet
Worksheet ws3 = new Worksheet("Лист 1");
//adding headers
string summary = "Згенеровано і розв'язано 5 задач різних розмірностей.";
ws3.AddCellWithStyle(0, 0, summary, s3.ID, 10, 0);
summary = "Граф представляється списком ребер. І і ІІ - вершини ребра.";
ws3.AddCellWithStyle(1, 0, summary, s3.ID, 10, 0);
summary = "Значення true у колонці алгоритму - ребро входить у розріз за результатом розбиття відповідним алгоритмом.";
ws3.AddCellWithStyle(2, 0, summary, s3.ID, 10, 0);
int totalRows = 4;
// appending rows with data
foreach (var table in resultTables)
{
ws3.AddCellWithStyle(totalRows, 0, "I", s1.ID);
ws3.AddCellWithStyle(totalRows, 1, "II", s1.ID);
ws3.AddCellWithStyle(totalRows, 2, "Вага", s1.ID);
ws3.AddCellWithStyle(totalRows, 3, "ФМ", s1.ID);
ws3.AddCellWithStyle(totalRows, 4, "Бджолиний", s1.ID);
for (int i = 0; i < table.Partition.Count; i++)
{
ws3.AddCell(totalRows + 1, 0, table.Partition[i].first_vertex);
ws3.AddCell(totalRows + 1, 1, table.Partition[i].second_vertex);
ws3.AddCell(totalRows + 1, 2, table.Partition[i].weight);
ws3.AddCell(totalRows + 1, 3, Convert.ToString(table.Partition[i].fmPartition));
ws3.AddCell(totalRows + 1, 4, Convert.ToString(table.Partition[i].beePartition));
totalRows++;
}
ws3.AddCellWithStyle(totalRows + 1, 0, "Розріз:", s2.ID);
//ws3.AddCellWithStyle(totalRows+1, 0, "ФМ: ", s2.ID);
ws3.AddCell(totalRows + 1, 1, "");
ws3.AddCell(totalRows + 1, 2, "");
ws3.AddCellWithStyle(totalRows + 1, 3, table.fm_cutWeight, s2.ID);
//totalRows++;
//totalRows++;
//ws3.AddCellWithStyle(totalRows+1, 0, "Бджолиний: ", s2.ID);
ws3.AddCellWithStyle(totalRows + 1, 4, table.bee_cutWeight, s2.ID);
totalRows++;
totalRows++;
totalRows++;
}
wb.AddWorksheet(ws3);
// generate xml
string workstring = wb.ExportToXML();
// Send to user file
return(new ExcelResult("ExpResult.xls", workstring));
}
public void TestMethod1()
{
var workbook = new Workbook();
var worksheet1 = workbook.AddSheet("sheet 1");
for (int row = 1; row < 10; row++)
{
for (int col = 0; col < 5; col++)
{
var cell = worksheet1.GetCell(row, col);
cell.Value = string.Format("{0}:{1}", row + 1, col + 1);
}
}
var worksheet2 = workbook.AddSheet("sheet 2");
var style1 = workbook.AddStyle("style1");
style1.AlignmentStyle.Horizontal = HorizontalAlignment.Center;
style1.AlignmentStyle.ShrinkToFit = true;
style1.Borders.Add("#000000", BorderLineStyle.Continuous);
style1.FontStyle.Bold = true;
style1.FontStyle.Family = FontFamily.Modern;
style1.FontStyle.FontName = "Arial";
style1.FontStyle.Size = 14;
style1.FontStyle.Shadow = true;
style1.FontStyle.Italic = true;
style1.FontStyle.Color = "#ff0000";
style1.InteriorStyle.Pattern = InteriorStylePattern.Solid;
style1.InteriorStyle.Color = "#e0e0e0";
var cellA1 = worksheet2["a1"];
cellA1.Value = "value a1";
cellA1.Style = "style1";
var cellA2 = worksheet2["a2"];
cellA2.Value = "value a2";
cellA2.Style = "style1";
var cellA3 = worksheet2["a3"];
cellA3.Value = "value a3";
cellA3.Style = "style1";
var cellB1 = worksheet2["b1"];
cellB1.Value = "value b1";
cellB1.Style = "style1";
var cellB2 = worksheet2["b2"];
cellB2.Value = "value b2";
cellB2.Style = "style1";
var cellB3 = worksheet2["b3"];
cellB3.Value = "value b3";
cellB3.Style = "style1";
// Assertions
var outerXml = workbook.OuterXml;
var path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
File.WriteAllText(path + @"\file2.xml", outerXml);
}
/// <summary>
/// Build Excel file
/// </summary>
/// <param name="items">product</param>
/// <param name="title"></param>
/// <returns></returns>
///
public ActionResult Export()
{
var result = string.Empty;
var wb = new Workbook();
// properties
wb.Properties.Author = "ABZ";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "ABZ";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// create style s1 for header
var s1 = new Style("s1")
{
Font = new Font {
Bold = true, Italic = true, Color = "#FF0000"
}
};
wb.AddStyle(s1);
// create style s2 for header
var s2 = new Style("s2")
{
Font = new Font {
Bold = true, Italic = true, Size = 12, Color = "#0000FF"
}
};
wb.AddStyle(s2);
var ws3 = new Worksheet("Реестр отпущенной продукции");
// Adding Headers
ws3.AddCell(0, 0, "ТТН № ", 0);
ws3.AddCell(0, 1, "Дата", 0);
ws3.AddCell(0, 2, "Время", 0);
ws3.AddCell(0, 3, "Продукция", 0);
ws3.AddCell(0, 4, "Кол", 0);
ws3.AddCell(0, 5, "Цена", 0);
ws3.AddCell(0, 6, "Сумма", 0);
ws3.AddCell(0, 7, "Модель", 0);
ws3.AddCell(0, 8, "Автобаза", 0);
ws3.AddCell(0, 9, "Гос. №", 0);
ws3.AddCell(0, 10, "Водитель", 0);
ws3.AddCell(0, 11, "Адрес", 0);
// get data
int dm = (int)Session["Month"];
int year = (int)Session["Year"];
//Установили 1е число
DateTime begDt = new DateTime(year, dm, 01, 0, 0, 0);
//Установили 1е число следующего месяца
DateTime endDt = begDt.AddMonths(1);
TtnRepository repo = new TtnRepository();
List <Ttn> ttn = repo.GetTtn(Cust.CustId, begDt, endDt);
int totalRows = 0;
// appending rows with data
for (int i = 0; i < ttn.Count; i++)
{
ws3.AddCell(i + 1, 0, ttn[i].Num, 0);
ws3.AddCell(i + 1, 1, ttn[i].Dat, 0);
ws3.AddCell(i + 1, 2, ttn[i].Tm, 0);
ws3.AddCell(i + 1, 3, ttn[i].Good, 0);
ws3.AddCell(i + 1, 4, ttn[i].Kol, 0);
ws3.AddCell(i + 1, 5, ttn[i].Price, 0);
ws3.AddCell(i + 1, 6, ttn[i].Sm, 0);
ws3.AddCell(i + 1, 7, ttn[i].Amodel, 0);
ws3.AddCell(i + 1, 8, ttn[i].Ab, 0);
ws3.AddCell(i + 1, 9, ttn[i].Gn, 0);
ws3.AddCell(i + 1, 10, ttn[i].Driv, 0);
ws3.AddCell(i + 1, 11, ttn[i].Adres, 0);
totalRows++;
}
totalRows++;
wb.AddWorksheet(ws3);
// generate xml
var workstring = wb.ExportToXML();
// Send to user file
return(new ExcelResult("Reestr.xlsx", workstring));
}