public void SkiperAuto()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("test");
int skip = rnd.Next(4) + 1;
for (int i = 0; i < skip; i++)
{
sheet.AddCell($"Caption{i + 1}", $"A{i + 1}", XlContentType.SharedString);
}
sheet.AddCell("Поле 1", $"A{++skip}", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", $"B{skip}", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", $"C{skip}", XlContentType.SharedString);
sheet.AddCell("дробь", $"E{skip}", XlContentType.SharedString);
for (int i = ++skip; i < skip + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", XlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(memstream);
XLOCReader convertor = XlConverter.FromStream(memstream, new XLOCConfiguration {
SkipMode = SkipModeEnum.Auto, SkipCount = 1
});
Assert.AreEqual(countShouldBe, convertor.ReadToArray <TestExcelClass>().Count());
}
}
public void MultiCaptionTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("sheet1");
sheet.AddCell("Поле 1", "A1", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок2", "D1", XlContentType.SharedString);
sheet.AddCell("дробь", "E1", XlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", XlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"Какая-то строка{i}", $"D{i}", XlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(memstream);
var isValid = true;
TestExcelClass[] data = XlConverter.FromStream(memstream, new XLOCConfiguration {
ValidationFailureEvent = (s, e) => isValid = false
}).ReadToEnumerable <TestExcelClass>().ToArray();
Assert.AreEqual(0, data.Count());
Assert.IsFalse(isValid);
}
}
public void AutoDisposing()
{
int countShouldBe = 4;
using (var ms = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("test");
sheet.AddCell("Поле 1", $"A1", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", $"B1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", $"C1", XlContentType.SharedString);
sheet.AddCell("дробь", $"AB1", XlContentType.SharedString);
sheet.AddCell("noize", $"AC1", XlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", XlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"{(i / 100M).ToString("E")}", $"AB{i}", XlContentType.Double);
sheet.AddCell($"noize", $"AC{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(ms);
IEnumerable <TestExcelClass> data = XlConverter.FromStream(ms, new XLOCConfiguration {
SkipMode = SkipModeEnum.Auto, ContinueOnRowReadingError = false, AutoDispose = false
}).ReadToEnumerable <TestExcelClass>();
Assert.AreEqual(countShouldBe, data.Count());
Assert.IsTrue(data.All(x => x.decimalProperty != 0));
}
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// we need some data to display
XlRange dataRange = PutSampleData(workSheet);
// create a nice diagram
XlChartObject chart = workSheet.ChartObjects.Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example5{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public void ValidationEventTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new XlBook();
const string sheetName = "sheet1";
XlSheet sheet = book.AddSheet(sheetName);
sheet.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", XlContentType.SharedString);
sheet.AddCell("дробь", "E1", XlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(memstream);
XlConverter.FromStream(memstream, new XLOCConfiguration {
ValidationFailureEvent = (s, e) => { if (!e.MissingFields.Contains("Поле 1") || e.Sheet.Name != sheetName)
{
Assert.Fail();
}
}
}).ReadToArray <TestExcelClass>();
TestExcelClass[] data = XlConverter.FromStream(memstream).ReadToEnumerable <TestExcelClass>().ToArray();
}
}
public void SkiperManual()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("test");
sheet.AddCell("Caption", "A1", XlContentType.SharedString);
sheet.AddCell("Caption2", "A2", XlContentType.SharedString);
sheet.AddCell("Поле 1", "A3", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B3", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок2", "D3", XlContentType.SharedString);
sheet.AddCell("дробь", "E3", XlContentType.SharedString);
for (int i = 4; i < 4 + countShouldBe; i++)
{
sheet.AddCell(i, $"A{i}", XlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(memstream);
XLOCReader convertor = XlConverter.FromStream(memstream, new XLOCConfiguration {
SkipMode = SkipModeEnum.Manual, SkipCount = 2
});
Assert.AreEqual(countShouldBe, convertor.ReadToArray <TestExcelClass>().Count());
}
}
public void Write()
{
DocumentFormat.OpenXml.Validation.ValidationErrorInfo[] err = XlConverter.FromEnumerable(data).SaveToFile(path);
if (err.Count() > 0)
{
Assert.Fail("Ошибка сохранения:\n{0}", string.Join("\n", err.Select(x => x.Description)));
}
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
/*do background color for cells*/
string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
// draw the face
string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator);
workSheet.Range(rangeAdressFace).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);
workSheet.Range(rangeAdressEyes).Interior.Color = XlConverter.ToDouble(Color.Black);
string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);
workSheet.Range(rangeAdressNoise).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);
workSheet.Range(rangeAdressMouth).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
/*do borderlines for cells*/
// border the face with the border arround method
workSheet.Range(rangeAdressFace).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressEyes).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressNoise).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDouble, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
// border explicitly
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Weight = 4;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Color = 400;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example1{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void ProceedSummaryMatrix(SalesReport report, XlWorksheet summarySheet, XlStyle matrixStyle)
{
// table columns
summarySheet.Range("B2").Value = "Count";
summarySheet.Range("C2").Value = "Revenue";
summarySheet.Range("D2").Value = "%";
summarySheet.Range("E2").Value = "Storage";
string leftBottomCellAdress = XlConverter.ToCellAdress(1, 3 + report.Products.Length);
string rightBottomCellAdress = XlConverter.ToCellAdress(5, 3 + report.Products.Length);
summarySheet.Range("$A2:$" + rightBottomCellAdress).Style = matrixStyle;
int rowIndex = 3;
int columnIndex = 1;
int i = 0;
foreach (SalesReportProduct itemProduct in report.Products)
{
string prodName = itemProduct.ProductName;
int prodId = itemProduct.ProductId;
summarySheet.Cells(rowIndex, columnIndex).Value = prodName;
string formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 13));
summarySheet.Cells(rowIndex, columnIndex + 1).Value = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 12));
summarySheet.Cells(rowIndex, columnIndex + 2).Value = formula;
formula = string.Format("={0}*100/{1}", XlConverter.ToCellAdress(3, rowIndex), XlConverter.ToCellAdress(3, 3 + report.Products.Length));
summarySheet.Cells(rowIndex, columnIndex + 3).Formula = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, "B6");
summarySheet.Cells(rowIndex, columnIndex + 4).Value = formula;
int storeCount = Convert.ToInt16(summarySheet.Cells(rowIndex, columnIndex + 4).Value);
if ((i % 2) == 0)
{
summarySheet.Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
}
rowIndex++;
i++;
}
string sumFormula = string.Format("=Sum({0}:{1})", "C3", "C" + (report.Products.Length + 3 - 1).ToString());
summarySheet.Cells(rowIndex, columnIndex + 2).Value = sumFormula;
summarySheet.Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €";
summarySheet.Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\"";
summarySheet.Cells(3 + report.Products.Length, 1).Value = "Total:";
summarySheet.Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter;
summarySheet.Range("$B2:$E2").Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new XlApplication();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
XlWorkbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
XlWorksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
XlWorksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.Range("$A2").Select();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void ProceedProductMonthInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
productSheet.Range("$A9:$M13").StyleAsString = "MonthInfos";
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = "ManufacturerPriceSummary";
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = "SalesPricesSummary";
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = "TotalRevenue";
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemMonth.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemMonth.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.Range("$B10:$" + cellAdress1).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$B12:$" + cellAdress2).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(_monthToReport - 1);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(i - 1);
}
productSheet.Range("$B9:$M9").NumberFormat = "";
productSheet.Range("$B9:$M9").Font.Bold = true;
productSheet.Range("$B13:$M13").NumberFormat = "";
productSheet.Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
public void Read()
{
Write();
TestExcelClass[] readedData = XlConverter.FromFile(path).ReadToEnumerable <TestExcelClass>().ToArray();
Assert.AreEqual(data.Count(), readedData.Count(), "Количество загруженных строк не совпадает");
for (int i = 0; i < data.Count(); i++)
{
Assert.AreEqual(data[i].intProperty1, readedData[i].intProperty1, "Поля заполены не верно");
Assert.AreEqual(data[i].intProperty2, readedData[i].intProperty2, "Поля заполены не верно");
Assert.AreEqual(data[i].SomeDate.ToShortDateString(), readedData[i].SomeDate.ToShortDateString(), "Поля заполены не верно");
Assert.AreEqual(data[i].SomeString, readedData[i].SomeString, "Поля заполены не верно");
}
}
public void ReadToGroup_DifferentClasses()
{
using (var ms = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("Sheet1");
char col = 'B';
sheet.AddCell("Поле 1", "A1", XlContentType.SharedString);
foreach (FieldGenerator item in fields)
{
sheet.AddCell(item.Caption, $"{item.Col = col++}1", XlContentType.SharedString);
}
for (int i = 2; i < 5; i++)
{
foreach (FieldGenerator item in fields)
{
sheet.AddCell(item.Filler(i), $"{item.Col}{i}", item.contentType);
}
}
sheet = book.AddSheet("Sheet2");
sheet.AddCell("MyProperty", "A1", XlContentType.SharedString);
sheet.AddCell("Test2UniqueField", "B1", XlContentType.SharedString);
for (int i = 2; i < 5; i++)
{
sheet.AddCell(i, $"A{i}", XlContentType.Integer);
sheet.AddCell(i * 2, $"B{i}", XlContentType.Integer);
}
XlConverter.FromBook(book).SaveToStream(ms);
XLOCReader reader = XlConverter.FromStream(ms, new XLOCConfiguration {
});
IEnumerable <IGrouping <SheetIdentifier, TestExcelClass> > res1 = reader.ReadToGroup <TestExcelClass>();
Assert.AreEqual(1, res1.Count(x => x.Any()));
Assert.AreEqual(3, res1.Single(x => x.Any()).Count());
IEnumerable <IGrouping <SheetIdentifier, TestExcelClass2> > res2 = reader.ReadToGroup <TestExcelClass2>();
Assert.AreEqual(1, res2.Count(x => x.Any()));
Assert.AreEqual(3, res2.Single(x => x.Any()).Count());
Assert.AreEqual(3, reader.ReadToEnumerable <TestExcelClass>().Count());
Assert.AreEqual(3, reader.ReadToEnumerable <TestExcelClass2>().Count());
}
}
public void WriteEmptyBook()
{
var book = new XlBook();
book.AddSheet("test1");
book.Sheets.First().AddCell(12.3, "B2", XlContentType.Double);
book.Sheets.First().AddCell(DateTime.Now, "C3", XlContentType.Date);
book.Sheets.First().AddCell("asdasd", "D4", XlContentType.SharedString);
var err = XlConverter.FromBook(book).SaveToStream(new System.IO.MemoryStream());
if (err.Count() > 0)
{
Assert.Fail("Ошибка сохранения:\n{0}", string.Join("\n", err.Select(x => x.Description)));
}
}
private void SetProductStorageCountColor(int storageCount, XlRange range)
{
if (storageCount <= 50)
{
range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Red);
}
else if (storageCount <= 100)
{
range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Yellow);
}
else
{
range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Green);
}
}
public void ReadToGroup_SameClass()
{
Dictionary <int, int> result = new Dictionary <int, int> {
};
using (var ms = new MemoryStream())
{
var book = new XlBook();
for (int i = 0; i < rnd.Next(3, 5); i++)
{
XlSheet sheet = book.AddSheet($"Sheet{i}");
FieldGenerator[] columns = fields.Where(x => rnd.Next(0, 5) < 5).ToArray();
var letter = 'B';
sheet.AddCell("Поле 1", "A1", XlContentType.SharedString);
foreach (FieldGenerator item in columns)
{
sheet.AddCell(item.Caption, $"{item.Col = letter++}1", XlContentType.SharedString);
}
result[i] = rnd.Next(4, 6);
for (int j = 2; j < 2 + result[i]; j++)
{
foreach (FieldGenerator item in columns)
{
sheet.AddCell(item.Filler(j), $"{item.Col}{j}", item.contentType);
}
}
}
XlConverter.FromBook(book).SaveToStream(ms);
IEnumerable <IGrouping <SheetIdentifier, TestExcelClass> > data = XlConverter.FromStream(ms, new XLOCConfiguration {
}).ReadToGroup <TestExcelClass>();
Assert.AreEqual(result.Count, data.Count(), "Количество листов не совпадает");
foreach (KeyValuePair <int, int> item in result)
{
IGrouping <SheetIdentifier, TestExcelClass> itemsCount = data.Single(x => x.Key.Name == $"Sheet{item.Key}");
Assert.AreEqual(item.Value, itemsCount.Count(), $"Количество записей на листе {item.Key} не соответсвует");
}
}
}
public void ReadToArrayWithNullableColumns()
{
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sh = book.AddSheet("sheet1");
#region Captions
//=================================================
sh.AddCell("Поле 1", "A1", XlContentType.SharedString);
sh.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sh.AddCell("Мультизагаловок2", "C1", XlContentType.SharedString);
sh.AddCell("дробь", "E1", XlContentType.SharedString);
sh.AddCell("Поле 3", "F1", XlContentType.SharedString);
//=================================================
#endregion
#region Data
//=================================================
sh.AddCell(1, "A2", XlContentType.SharedString);
sh.AddCell(DateTime.Now, "B2", XlContentType.Date);
sh.AddCell("Какая-то строка", "C2", XlContentType.SharedString);
sh.AddCell("0.15", "E2", XlContentType.Double);
sh.AddCell("", "F2", XlContentType.SharedString);
sh.AddCell(2, "A3", XlContentType.Integer);
sh.AddCell(DateTime.Now, "B3", XlContentType.Date);
sh.AddCell("Какая-то строка", "C3", XlContentType.SharedString);
sh.AddCell("0.25", "E3", XlContentType.Double);
sh.AddCell("", "F3", XlContentType.SharedString);
//=================================================
#endregion
XlConverter.FromBook(book).SaveToStream(memstream);
TestExcelClass[] data = XLOC.XlConverter.FromStream(memstream, new XLOCConfiguration {
CellReadingErrorEvent = (s, e) => { throw new Exception(e.Exception.Message); }
}).ReadToArray <TestExcelClass>();
Assert.AreEqual(2, data.Count());
Assert.IsTrue(data.All(x => !x.intProperty3.HasValue));
}
}
private void ProceedProductYearTotalInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int ColumnIndex = 15;
int RowIndex = 9;
productSheet.Range("$O9:$R13").StyleAsString = "YearTotalInfos";
productSheet.Cells(RowIndex, ColumnIndex).Value = "Year " + _yearToReport.ToString();
productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Year.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Year.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Year.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Year.CountOfSales;
productSheet.Cells(RowIndex, ColumnIndex + 1).Value = "Year " + (_yearToReport - 1).ToString();
productSheet.Cells(RowIndex + 1, ColumnIndex + 1).Value = itemProduct.PrevYear.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex + 1).Value = itemProduct.PrevYear.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex + 1).Value = itemProduct.PrevYear.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex + 1).Value = itemProduct.PrevYear.CountOfSales;
productSheet.Range("$O10:$P10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$O12:$P12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
ColumnIndex = 18;
RowIndex = 9;
productSheet.Cells(RowIndex, ColumnIndex).Value = "Total";
productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Total.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Total.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Total.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Total.CountOfSales;
productSheet.Range("$R10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$R12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$O9:$R9").NumberFormat = "";
productSheet.Range("$O9:$R9").Font.Bold = true;
productSheet.Range("$O13:$R13").NumberFormat = "";
productSheet.Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
productSheet.Range("$Q9:$Q13").MergeCells = true;
}
public void TestMethod1()
{
var book = new XlBook();
XlSheet sh = book.AddSheet("sheet1");
sh.AddCell("Field", "A1", XlContentType.SharedString);
sh.AddCell("1", "A2", XlContentType.SharedString);
sh.AddCell(1, "A3", XlContentType.Double);
sh.AddCell(1, "A4", XlContentType.Integer);
sh.AddCell(1, "A5", XlContentType.Void);
var memstream = new MemoryStream();
DocumentFormat.OpenXml.Validation.ValidationErrorInfo[] err = XlConverter.FromBook(book).SaveToStream(memstream);
Assert.IsFalse(err.Any(), string.Join("\n", err.Select(x => x.Description)));
MyClass[] result = XlConverter.FromStream(memstream).ReadToArray <MyClass>();
Assert.AreEqual(4, result.Count());
Assert.IsTrue(result.All(x => x.Field == 1));
}
public void SaveBookThroughFile()
{
if (string.IsNullOrWhiteSpace(xl.Name))
{
NameSet();
}
if (xl.Sheets.Count() == 0)
{
AddSheet();
}
try
{
DocumentFormat.OpenXml.Validation.ValidationErrorInfo[] err = XlConverter.FromBook(xl).SaveToFile(string.Format(@"{0}\{1}", Path.Combine(Environment.CurrentDirectory), xl.Name));
if (err.Count() > 0)
{
Assert.Fail("Ошибка сохранения:\n{0}", string.Join("\n", err.Select(x => x.Description)));
}
}
catch (Exception ex) { Assert.Fail(string.Format("Ошибка сохранения\n{0}", ex.Message)); }
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
XlVBComponent globalModule = workBook.VBProject.VBComponents.Add(LateBindingApi.Excel.Enums.vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello World!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
workBook.Worksheets[1].Cells(2, 2).Value = "This workbook contains dynamic created VBA Moduls and Event Code";
workBook.Worksheets[1].Cells(5, 2).Value = "Open the VBA Editor to see the code";
workBook.Worksheets[1].Cells(8, 2).Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example7{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
workSheet.Cells(1, 1).Value = "these sample shapes was dynamicly created by code.";
// create a star
XlShape starShape = workSheet.Shapes.AddShape(LateBindingApi.Excel.Enums.MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
XlShape textBox = workSheet.Shapes.AddTextbox(LateBindingApi.Excel.Enums.MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
XlShape textEffect = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, LateBindingApi.Excel.Enums.MsoTriState.msoTrue, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 250);
// create text effect
XlShape textDiagram = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example4{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public void ReadToGroup_SameColumns()
{
Dictionary <int, int> result = new Dictionary <int, int> {
};
using (var ms = new MemoryStream())
{
var book = new XlBook();
for (int i = 0; i < rnd.Next(3, 5); i++)
{
XlSheet sheet = book.AddSheet($"Sheet{i}");
sheet.AddCell("Поле 1", "A1", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", XlContentType.SharedString);
sheet.AddCell("дробь", "E1", XlContentType.SharedString);
result[i] = rnd.Next(4, 6);
for (int j = 2; j < 2 + result[i]; j++)
{
sheet.AddCell(j, $"A{j}", XlContentType.Integer);
sheet.AddCell(DateTime.Now, $"B{j}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{j}", $"C{j}", XlContentType.SharedString);
sheet.AddCell($"0.1{j}", $"E{j}", XlContentType.Double);
}
}
XlConverter.FromBook(book).SaveToStream(ms);
IEnumerable <IGrouping <SheetIdentifier, TestExcelClass> > data = XlConverter.FromStream(ms, new XLOCConfiguration {
}).ReadToGroup <TestExcelClass>();
Assert.AreEqual(result.Count, data.Count(), "Количество листов не совпадает");
foreach (KeyValuePair <int, int> item in result)
{
IGrouping <SheetIdentifier, TestExcelClass> itemsCount = data.Single(x => x.Key.Name == $"Sheet{item.Key}");
Assert.AreEqual(item.Value, itemsCount.Count(), $"Количество записей на листе {item.Key} не соответсвует");
}
}
}
public void ReadToArrayWithoutNullableColumns()
{
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sh = book.AddSheet("sheet1");
#region Captions
//=================================================
sh.AddCell("Поле 1", "A1", XlContentType.SharedString);
sh.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sh.AddCell("Мультизагаловок2", "C1", XlContentType.SharedString);
sh.AddCell("дробь", "E1", XlContentType.SharedString);
//=================================================
#endregion
#region Data
//=================================================
sh.AddCell(1, "A2", XlContentType.Integer);
sh.AddCell(DateTime.Now, "B2", XlContentType.Date);
sh.AddCell("Какая-то строка", "C2", XlContentType.SharedString);
sh.AddCell("0.15", "E2", XlContentType.Double);
sh.AddCell(2, "A3", XlContentType.Integer);
sh.AddCell(DateTime.Now, "B3", XlContentType.Date);
sh.AddCell("Какая-то строка", "C3", XlContentType.SharedString);
sh.AddCell("0.25", "E3", XlContentType.Double);
//=================================================
#endregion
XlConverter.FromBook(book).SaveToStream(memstream);
TestExcelClass[] data = XlConverter.FromStream(memstream).ReadToEnumerable <TestExcelClass>().ToArray();
Assert.AreEqual(2, data.Count());
Assert.IsTrue(data.All(x => !x.intProperty2.HasValue));
}
}
public void CellEventTest()
{
int countShouldBe = 4;
using (var memstream = new MemoryStream())
{
var book = new XlBook();
XlSheet sheet = book.AddSheet("sheet1");
sheet.AddCell("Поле 1", "A1", XlContentType.SharedString);
sheet.AddCell("Какая-то дата", "B1", XlContentType.SharedString);
sheet.AddCell("Мультизагаловок1", "C1", XlContentType.SharedString);
sheet.AddCell("дробь", "E1", XlContentType.SharedString);
for (int i = 2; i < 2 + countShouldBe; i++)
{
sheet.AddCell("A", $"A{i}", XlContentType.SharedString);
sheet.AddCell(DateTime.Now, $"B{i}", XlContentType.Date);
sheet.AddCell($"Какая-то строка{i}", $"C{i}", XlContentType.SharedString);
sheet.AddCell($"0.1{i}", $"E{i}", XlContentType.Double);
}
XlConverter.FromBook(book).SaveToStream(memstream);
bool result = true;
XlConverter.FromStream(memstream, new XLOCConfiguration {
CellReadingErrorEvent = (s, e) => { if (e.Reference != "A2")
{
result = false;
}
}, AutoDispose = false
}).ReadToArray <TestExcelClass>();
Assert.IsFalse(result);
TestExcelClass[] data = XlConverter.FromStream(memstream, new XLOCConfiguration {
AutoDispose = true
}).ReadToArray <TestExcelClass>();
}
}
private void ProceedSummaryWorksheetCharts(XlWorksheet summarySheet, int countOfProducts)
{
string captionRangeAdress = "$A2:$" + XlConverter.ToCellAdress(1, 1 + countOfProducts);
string fieldRangeAdress = "$C2:$" + XlConverter.ToCellAdress(3, 1 + countOfProducts);
double chartTopPosition = summarySheet.Rows[countOfProducts + 5].Top;
double chartWidth = summarySheet.Columns[13].Left;
XlChartObject chartSummary = summarySheet.ChartObjects.Add(1, chartTopPosition, chartWidth, 260);
chartSummary.Chart.SetSourceData(summarySheet.Range(captionRangeAdress + ";" + fieldRangeAdress));
fieldRangeAdress = "$D2:$" + XlConverter.ToCellAdress(4, 1 + countOfProducts);
chartTopPosition = summarySheet.Rows[2].Top;
double chartLeftPosition = summarySheet.Columns[8].Left;
double chartHeight = summarySheet.Rows[countOfProducts + 3].Top - chartTopPosition;
chartWidth = summarySheet.Columns[13].Left - summarySheet.Columns[8].Left;
XlChartObject chartPercentOutcome = summarySheet.ChartObjects.Add(chartLeftPosition, chartTopPosition, chartWidth, chartHeight);
chartPercentOutcome.Chart.ChartType = XlChartType.xlPie;
chartPercentOutcome.Chart.SetSourceData(summarySheet.Range(captionRangeAdress + ";" + fieldRangeAdress));
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// font action
workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.Range("A1").Font.Name = "Arial";
workSheet.Range("A1").Font.Size = 8;
workSheet.Range("A1").Font.Bold = true;
workSheet.Range("A1").Font.Italic = true;
workSheet.Range("A1").Font.Underline = true;
workSheet.Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.Range("A3").Value = "Times New Roman Size:10";
workSheet.Range("A3").Font.Name = "Times New Roman";
workSheet.Range("A3").Font.Size = 10;
workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.Range("A5").Font.Name = "Comic Sans MS";
workSheet.Range("A5").Font.Size = 12;
workSheet.Range("A5").WrapText = true;
workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.Range("A7").Value = "xlHAlignLeft";
workSheet.Range("A7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignDistributed;
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 35;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example2{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// some kind of numerics
string Pattern1 = string.Format("0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator, XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyGroupSeparator);
workSheet.Range("A1").Value = "Type";
workSheet.Range("B1").Value = "Value";
workSheet.Range("C1").Value = "Formatted " + Pattern1;
workSheet.Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.Range("A3").Value = "Integer";
workSheet.Range("B3").Value = integerValue;
workSheet.Range("C3").Value = integerValue;
workSheet.Range("C3").NumberFormat = Pattern1;
workSheet.Range("D3").Value = integerValue;
workSheet.Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.Range("A4").Value = "double";
workSheet.Range("B4").Value = doubleValue;
workSheet.Range("C4").Value = doubleValue;
workSheet.Range("C4").NumberFormat = Pattern1;
workSheet.Range("D4").Value = doubleValue;
workSheet.Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.Range("A5").Value = "float";
workSheet.Range("B5").Value = floatValue;
workSheet.Range("C5").Value = floatValue;
workSheet.Range("C5").NumberFormat = Pattern1;
workSheet.Range("D5").Value = floatValue;
workSheet.Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.Range("A6").Value = "Decimal";
workSheet.Range("B6").Value = decimalValue;
workSheet.Range("C6").Value = decimalValue;
workSheet.Range("C6").NumberFormat = Pattern1;
workSheet.Range("D6").Value = decimalValue;
workSheet.Range("D6").NumberFormat = Pattern2;
workSheet.Range("A9").Value = "DateTime";
workSheet.Range("B10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;
// string
workSheet.Range("A14").Value = "String";
workSheet.Range("B14").Value = "This is a sample String";
workSheet.Range("B14").NumberFormat = "@";
// number as string
workSheet.Range("B15").Value = "513";
workSheet.Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example3{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
