public void NoRowsTest()
{
var book = new ExcelBook("excel-sharp.xlsx");
var sheet = book.GetSheet("Fetch");
var models = sheet.Fetch <DateFetchModel>("A2", x => new { x.Name, x.Date, x.StringDate, x.NumberDate, x.FormulaDate });
Assert.Empty(models);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
int i = 0;
int k = 1, h = 1;
GetDataGridRows(bookingDataGrid);
var rows = GetDataGridRows(bookingDataGrid);
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelBook;
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;
ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
for (i = 1; i <= bookingDataGrid.Columns.Count; i++)
{
ExcelSheet.Cells[1, i] = bookingDataGrid.Columns[i - 1].Header.ToString();
}
foreach (DataGridRow r in rows)
{
foreach (DataGridColumn column in bookingDataGrid.Columns)
{
if (column.GetCellContent(r) is TextBlock)
{
TextBlock cellContent = column.GetCellContent(r) as TextBlock;
ExcelSheet.Cells[h + 1, k] = cellContent.Text.Trim();
k++;
}
}
k = 1;
h++;
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Excel File (.xls)|*.xls";
if (dlg.ShowDialog() == true)
{
ExcelBook.SaveAs(dlg.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlUserResolution, true,
Missing.Value, Missing.Value, Missing.Value);
ExcelBook.Close(dlg.FileName, Missing.Value, Missing.Value);
ExcelSheet = null;
ExcelBook = null;
ExcelApp = null;
MessageBox.Show("Excel File has been generated !!!");
}
else
{
MessageBox.Show("Failed to generate Excel file !!!");
}
}
public void Test1()
{
var book = new ExcelBook(ExcelVersion.Excel2007);
var 黑体style2 = book.CStyle(x => x.CellFormat("0.00").CellColor(RGBColor.Blue).SetFont("黑体", 20).FullBorder());
var 黑体style1 = book.CStyle(x => x.CellFormat("0.00").CellColor(RGBColor.Blue).SetFont("黑体", 20).FullBorder());
var 宋体style1 = book.CStyle(x => x.CellFormat("0.00").CellColor(RGBColor.Red).SetFont("宋体", 27, RGBColor.BlueGrey).FullBorder());
var sampleSheet = book.CreateSheet("Sample");
sampleSheet.SetWidth("A", 8.5);
sampleSheet.SetWidth("B", 3);
sampleSheet.SetWidth("C", 30);
sampleSheet.SetCursor("C2");
sampleSheet.PrintLine("Suplier", "Product", "Quantity", "Release Date", "Describe");
sampleSheet.Print(new object[, ]
{
{ "Company 101", "Ag", 1, DateTime.Parse("2017-1-1"), "[??]" },
{ "Company 101", "Ag", 1, DateTime.Parse("2017-1-1"), "[??]" },
{ "Company 101", "Ag", 2, DateTime.Parse("2017-1-2"), "[??]" },
{ "Company 101", "Ag", 2, DateTime.Parse("2017-1-2"), "[??]" },
{ "Company 102", "Ag", 3, DateTime.Parse("2017-1-3"), "[??]" },
{ "Company 102", "Cu", 3, DateTime.Parse("2017-1-3"), "[??]" },
{ "Company 102", "Cu", 4, DateTime.Parse("2017-1-4"), "[??]" },
}).Then(range =>
{
range.SetCStyle(黑体style1);
var cstyle = range.ToArray()[0].GetCStyle();
var applier = cstyle.GetApplier();
Assert.Equal("0.00", applier.DataFormat);
foreach (var row in range.GetRows())
{
if (row[(0, 1)].GetValue().ToString() == "Cu")
{
row.Column(1).SetCStyle(宋体style1);
}
}
public Errors OpenExcel(string filePath)
{
visible = false;
if (!File.Exists(filePath))
{
if (currExcel == filePath)
{
currExcel = null;
opened = false;
if (app != null)
{
app.Visible = false;
}
}
return(Errors.no_file);
}
if (currExcel == filePath)
{
return(0);
}
if (app == null)
{
app = new ExcelApp();
}
currExcel = filePath;
fileName = Path.GetFileNameWithoutExtension(filePath);
//if (app.Worksheets.Count > 0)
// app.Workbooks.Close();
object miss = Missing.Value;
book = app.Workbooks.Open(filePath, miss, true, miss, miss);
sheet = book.Worksheets.Item[1];
opened = sheet != null;
if (opened)
{
app.Visible = visible;
}
return(0);
}
public void NullableDateFetchTest()
{
var book = new ExcelBook("excel-sharp.xlsx");
var sheet = book.GetSheet("Fetch");
var models = sheet.Fetch <NullableDateFetchModel>("A11", x => new { x.Name, x.Date, x.StringDate, x.NumberDate, x.FormulaDate });
var date = new DateTime(2000, 1, 1);
var valueModel = models[0];
Assert.Equal(date, valueModel.Date);
Assert.Equal(date, valueModel.StringDate);
Assert.Equal(date, valueModel.NumberDate);
Assert.Equal(date, valueModel.FormulaDate);
var blankModel = models[1];
Assert.Null(blankModel.Date);
Assert.Null(blankModel.StringDate);
Assert.Null(blankModel.NumberDate);
Assert.Null(blankModel.FormulaDate);
}
public void NumberFetchTest()
{
var book = new ExcelBook("excel-sharp.xlsx");
var sheet = book.GetSheet("Fetch");
var models = sheet.Fetch <NumberFetchModel>("A16", x => new { x.Name, x.Byte, x.Char, x.Int16, x.UInt16, x.Int32, x.UInt32, x.Int64, x.UInt64, x.Single, x.Double, x.Decimal, x.Formula });
var valueModel = models[0];
Assert.Equal(100, valueModel.Byte);
Assert.Equal(100, valueModel.Char);
Assert.Equal(100, valueModel.Int16);
Assert.Equal(100, valueModel.UInt16);
Assert.Equal(100, valueModel.Int32);
Assert.Equal(100u, valueModel.UInt32);
Assert.Equal(100, valueModel.Int64);
Assert.Equal(100uL, valueModel.UInt64);
Assert.Equal(100, valueModel.Single);
Assert.Equal(100, valueModel.Double);
Assert.Equal(100m, valueModel.Decimal);
Assert.Equal(100, valueModel.Formula);
var blankModel = models[1];
Assert.Equal(0, blankModel.Byte);
Assert.Equal(0, blankModel.Char);
Assert.Equal(0, blankModel.Int16);
Assert.Equal(0, blankModel.UInt16);
Assert.Equal(0, blankModel.Int32);
Assert.Equal(0u, blankModel.UInt32);
Assert.Equal(0, blankModel.Int64);
Assert.Equal(0uL, blankModel.UInt64);
Assert.Equal(0, blankModel.Single);
Assert.Equal(0, blankModel.Double);
Assert.Equal(0, blankModel.Decimal);
Assert.Equal(0, blankModel.Formula);
}
