private void FastExcelReadDemo(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO READ 1");
Stopwatch stopwatch = Stopwatch.StartNew();
// Open excel file using read only is much faster, but you cannot perfrom any writes
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
Console.WriteLine("Reading data (Read Only Access) still needs enumerating...");
Worksheet worksheet = fastExcel.Read("sheet1", 1);
}
Console.WriteLine(string.Format("Reading data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWriteAddRowDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 3");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
Worksheet worksheet = new Worksheet();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
worksheet.AddRow(1 * DateTime.Now.Millisecond
, 2 * DateTime.Now.Millisecond
, 3 * DateTime.Now.Millisecond
, 4 * DateTime.Now.Millisecond
, 45678854
, 87.01d
, "Test 2" + rowNumber
, DateTime.Now.ToLongTimeString());
}
stopwatch.Start();
Console.WriteLine("Writing using AddRow(params object[])...");
fastExcel.Write(worksheet, "sheet4");
}
Console.WriteLine(string.Format("Writing using AddRow(params object[]) took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void GenerateReportExcel(string path, List <Models.Device.Device> devices)
{
var name = $"DataReport-{DateTime.Now.ToString("MM-dd-yyyy-HH-mm-ss")}.xlsx";
path = Path.Combine(path, name);
var templatePath = Path.Combine(Directory.GetCurrentDirectory(), "Template.xlsx");
var fi = new FileInfo(path);
var tfi = new FileInfo(templatePath);
Worksheet worksheet = new Worksheet();
List <Cell> celss = new List <Cell>()
{
new Cell(1, "Serial number"),
new Cell(2, "Model"),
new Cell(3, "Group"),
new Cell(4, "Current status"),
new Cell(5, "Events"),
new Cell(6, "Last event"),
new Cell(7, "Current client"),
};
List <Row> rows = new List <Row>()
{
new Row(1, celss)
};
for (int i = 0; i < devices.Count; i++)
{
var cells = new List <Cell>()
{
new Cell(1, devices[i].SerialNumber),
new Cell(2, devices[i].Model),
new Cell(3, devices[i].Classification),
new Cell(4, devices[i].LastStatus),
new Cell(5, devices[i].DeviceEventsCount),
new Cell(6, devices[i].DeviceEvents.OrderByDescending(x => x.Date).First().Date.ToShortDateString()),
new Cell(7, devices[i].DeviceEvents.OrderByDescending(x => x.Date).First().Location),
};
rows.Add(new Row(i + 2, cells));
}
worksheet.Rows = rows.ToArray();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(tfi, fi))
{
fastExcel.Write(worksheet, "Lapas1");
}
File.Open(path, FileMode.Open);
}
/// <summary>
/// Read the worksheet
/// </summary>
/// <param name="existingHeadingRows"></param>
public void Read(int existingHeadingRows = 0)
{
FastExcel.CheckFiles();
FastExcel.PrepareArchive();
ExistingHeadingRows = existingHeadingRows;
IEnumerable <Row> rows = null;
var headings = new List <string>();
using (Stream stream = FastExcel.Archive.GetEntry(FileName).Open())
{
var document = XDocument.Load(stream);
int skipRows = 0;
var rowElement = document.Descendants().Where(d => d.Name.LocalName == "row").FirstOrDefault();
if (rowElement != null)
{
var possibleHeadingRow = new Row(rowElement, this);
if (ExistingHeadingRows == 1 && possibleHeadingRow.RowNumber == 1)
{
foreach (var headerCell in possibleHeadingRow.Cells)
{
headings.Add(headerCell.Value.ToString());
}
}
}
rows = GetRows(document.Descendants().Where(d => d.Name.LocalName == "row").Skip(skipRows));
}
Headings = headings;
Rows = rows;
}
internal void ValidateNewWorksheet(FastExcel fastExcel, int?insertAfterSheetNumber = null, string insertAfterSheetName = null)
{
if (string.IsNullOrEmpty(this.Name))
{
// TODO possibly could calulcate a new worksheet name
throw new Exception("Name for new worksheet is not specified");
}
// Get worksheet details
Worksheet previousWorksheet = new Worksheet();
bool isNameValid = previousWorksheet.GetWorksheetPropertiesAndValidateNewName(fastExcel, insertAfterSheetNumber, insertAfterSheetName, this.Name);
this.InsertAfterIndex = previousWorksheet.Index;
if (!isNameValid)
{
throw new Exception(string.Format("Worksheet name '{0}' already exists", this.Name));
}
fastExcel.MaxSheetNumber += 1;
this.Index = fastExcel.MaxSheetNumber;
if (string.IsNullOrEmpty(this.Headers))
{
this.Headers = DEFAULT_HEADERS;
}
if (string.IsNullOrEmpty(this.Footers))
{
this.Footers = DEFAULT_FOOTERS;
}
}
/*private void FastExcelAddWorksheet(FileInfo outputFile)
* {
*
* Console.WriteLine();
* Console.WriteLine("DEMO ADD");
*
* Stopwatch stopwatch = new Stopwatch();
*
* using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
* {
* Worksheet worksheet = new Worksheet();
* worksheet.Name = "Sheet77";
*
* List<GenericObject> objectList = new List<GenericObject>();
*
* for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
* {
* GenericObject genericObject = new GenericObject();
* genericObject.IntegerColumn1 = 1 * DateTime.Now.Millisecond;
* genericObject.IntegerColumn2 = 2 * DateTime.Now.Millisecond;
* genericObject.IntegerColumn3 = 3 * DateTime.Now.Millisecond;
* genericObject.IntegerColumn4 = 4 * DateTime.Now.Millisecond;
* genericObject.IntegerColumn5 = 45678854;
* genericObject.DoubleColumn6 = 87.01d;
* genericObject.StringColumn7 = "Test 3" + rowNumber;
* genericObject.ObjectColumn8 = DateTime.Now.ToLongTimeString();
*
* objectList.Add(genericObject);
* }
* worksheet.PopulateRows(objectList);
*
* stopwatch.Start();
* Console.WriteLine("Writing using IEnumerable<MyObject>...");
* fastExcel.Add(worksheet, "sheet3");
* }
*
* Console.WriteLine(string.Format("Writing IEnumerable<MyObject> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
* }*/
#region Write Demos
private void FastExcelWriteDemo(FileInfo templateFile, FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 1");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFile))
{
Worksheet worksheet = new Worksheet();
List <Row> rows = new List <Row>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
List <Cell> cells = new List <Cell>();
cells.Add(new Cell(1, 1 * DateTime.Now.Millisecond));
cells.Add(new Cell(2, 2 * DateTime.Now.Millisecond));
cells.Add(new Cell(3, 3 * DateTime.Now.Millisecond));
cells.Add(new Cell(4, 4 * DateTime.Now.Millisecond));
cells.Add(new Cell(5, 45678854));
cells.Add(new Cell(6, 87.01d));
cells.Add(new Cell(7, "Test 1 " + rowNumber));
cells.Add(new Cell(8, DateTime.Now.ToLongTimeString()));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
stopwatch.Start();
Console.WriteLine("Writing data...");
fastExcel.Write(worksheet, "sheet1");
}
Console.WriteLine(string.Format("Writing data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWriteGenericsDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 2");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
List <GenericObject> objectList = new List <GenericObject>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
GenericObject genericObject = new GenericObject();
genericObject.IntegerColumn1 = 1 * DateTime.Now.Millisecond;
genericObject.IntegerColumn2 = 2 * DateTime.Now.Millisecond;
genericObject.IntegerColumn3 = 3 * DateTime.Now.Millisecond;
genericObject.IntegerColumn4 = 4 * DateTime.Now.Millisecond;
genericObject.IntegerColumn5 = 45678854;
genericObject.DoubleColumn6 = 87.01d;
genericObject.StringColumn7 = "Test 3" + rowNumber;
genericObject.ObjectColumn8 = DateTime.Now.ToLongTimeString();
objectList.Add(genericObject);
}
stopwatch.Start();
Console.WriteLine("Writing using IEnumerable<MyObject>...");
fastExcel.Write(objectList, "sheet3", true);
}
Console.WriteLine(string.Format("Writing IEnumerable<MyObject> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWriteAddRowDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 3");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
Worksheet worksheet = new Worksheet();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
worksheet.AddRow(1 * DateTime.Now.Millisecond
, 2 * DateTime.Now.Millisecond
, 3 * DateTime.Now.Millisecond
, 4 * DateTime.Now.Millisecond
, 45678854
, 87.01d
, "Test 2" + rowNumber
, DateTime.Now.ToLongTimeString());
}
stopwatch.Start();
Console.WriteLine("Writing using AddRow(params object[])...");
fastExcel.Write(worksheet, "sheet4");
}
Console.WriteLine(string.Format("Writing using AddRow(params object[]) took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWrite2DimensionArrayDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 4");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
List <object[]> rowData = new List <object[]>();
// Note rowNumber starts at 2 because we are using existing headings on the sheet
for (int rowNumber = 2; rowNumber < NumberOfRecords; rowNumber++)
{
rowData.Add(new object[] { 1 * DateTime.Now.Millisecond
, 2 * DateTime.Now.Millisecond
, 3 * DateTime.Now.Millisecond
, 4 * DateTime.Now.Millisecond
, 45678854
, 87.01d
, "Test 2" + rowNumber
, DateTime.Now.ToLongTimeString() });
}
stopwatch.Start();
Console.WriteLine("Writing using IEnumerable<IEnumerable<object>>...");
// Note existingHeadingRows = 1, because we are keeping existing headings on the sheet
fastExcel.Write(rowData, "sheet2", 1);
}
Console.WriteLine(string.Format("Writing using IEnumerable<IEnumerable<object>> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
public List <WindData> ReadFromExcel(string path)
{
// Get the input file paths
FileInfo inputFile = new FileInfo(path);
//Create a worksheet
FastExcel.Worksheet worksheet = null;
// Create an instance of Fast Excel
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
// Read the rows using worksheet name
worksheet = fastExcel.Read("Sheet1");
worksheet = fastExcel.Read(1);
list = worksheet.Rows.ToArray().ToList();
foreach (var item in list)
{
windData = new WindData();
foreach (var cellItem in item.Cells)
{
if (item.RowNumber != 1)
{
switch (cellItem.ColumnNumber)
{
case 1:
windData.year = cellItem.Value.ToString();
break;
case 2:
windData.month = cellItem.Value.ToString();
break;
case 3:
windData.day = cellItem.Value.ToString();
break;
case 4:
windData.timeUTC = cellItem.Value.ToString();
break;
case 5:
windData.windSpeed = cellItem.Value.ToString();
break;
default:
break;
}
}
}
if (item.RowNumber != 1)
{
listData.Add(FixDirection(windData));
}
}
}
return(listData);
}
/// <summary>
/// Loads, parses and validates redirects
/// </summary>
public void Load()
{
inputFile = new FileInfo(FileName);
Worksheet worksheet = null;
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
File = fastExcel.Read(1);
Redirects = File.Rows.Select(Parse).ToList();
}
Validate();
}
public FileInfo ToExcel()
{
var stopwatch = new Stopwatch();
using (var fastExcel = new FastExcel.FastExcel(templateFile, outputFile))
{
var worksheet = new Worksheet {
Rows = dataRows
};
stopwatch.Start();
fastExcel.Write(worksheet, "sheet1");
}
outputFile.Refresh();
return(outputFile);
}
private void FastExcelReadDemo2(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO READ 2");
Stopwatch stopwatch = Stopwatch.StartNew();
// Open excel file using read/write is slower, but you can also perform writes
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, false))
{
Console.WriteLine("Reading data (Read/Write Access) still needs enumerating...");
Worksheet worksheet = fastExcel.Read("sheet1", 1);
}
Console.WriteLine(string.Format("Reading data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelReadDemo(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO READ 1");
Stopwatch stopwatch = Stopwatch.StartNew();
// Open excel file using read only is much faster, but you cannot perfrom any writes
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
Console.WriteLine("Reading data (Read Only Access) still needs enumerating...");
Worksheet worksheet = fastExcel.Read("sheet1", 1);
}
Console.WriteLine(string.Format("Reading data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void readPlanillasExcelFile()
{
// Get the input file paths
FileInfo inputFile = new FileInfo(@"D:\SGH_Sistemas\ShiolExcelService\ShiolWinSvc\FastExcel\FastExcelDemo\bin\Debug\SHIOL 1-31 FINAL.XLSX");
//Create a worksheet
Worksheet worksheet = null;
// Create an instance of Fast Excel
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
// Read the rows using worksheet name
worksheet = fastExcel.Read("RH");
List <Field> fields = new List <Field>();
Boolean header = false;
foreach (Row row in worksheet.Rows)
{
if (!header)
{
int index = 0;
foreach (Cell cell in row.Cells)
{
fields.Add(new Field(cell.Value.ToString(), index));
}
header = true;
continue;
}
foreach (Cell cell in row.Cells)
{
Console.WriteLine(fields[cell.ColumnNumber - 1].Name + " -- " + cell.Value.ToString() + " -- " + cell.GetType());
}
Console.ReadKey();
}
// Read the rows using the worksheet index
// Worksheet indexes are start at 1 not 0
// This method is slightly faster to find the underlying file (so slight you probably wouldn't notice)
//worksheet = fastExcel.Read(1);
}
}
internal void Export(string[][] exportTable, string outFileName, string templateFileName = "")
{
var worksheet = new Worksheet();
var rows = new List <Row>();
for (int row = 0; row < exportTable.Length; row++)
{
List <Cell> cells = new List <Cell>();
for (int col = 0; col < exportTable[row].Length; col++)
{
cells.Add(new Cell(col + 1, exportTable[row][col]));
}
rows.Add(new Row(row + 1, cells));
}
worksheet.Rows = rows;
var templateFile = new FileInfo(templateFileName);
var outFile = new FileInfo(outFileName);
if (File.Exists(outFile.FullName))
{
File.Delete(outFile.FullName);
}
string worksheetName = "";
//Read worksheet
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile))
{
worksheetName = fastExcel.Worksheets[0].Name;
}
// Create an instance of FastExcel
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outFile))
{
// Write the data
fastExcel.Write(worksheet, worksheetName);
}
}
/*private void FastExcelAddWorksheet(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO ADD");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
Worksheet worksheet = new Worksheet();
worksheet.Name = "Sheet77";
List<GenericObject> objectList = new List<GenericObject>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
GenericObject genericObject = new GenericObject();
genericObject.IntegerColumn1 = 1 * DateTime.Now.Millisecond;
genericObject.IntegerColumn2 = 2 * DateTime.Now.Millisecond;
genericObject.IntegerColumn3 = 3 * DateTime.Now.Millisecond;
genericObject.IntegerColumn4 = 4 * DateTime.Now.Millisecond;
genericObject.IntegerColumn5 = 45678854;
genericObject.DoubleColumn6 = 87.01d;
genericObject.StringColumn7 = "Test 3" + rowNumber;
genericObject.ObjectColumn8 = DateTime.Now.ToLongTimeString();
objectList.Add(genericObject);
}
worksheet.PopulateRows(objectList);
stopwatch.Start();
Console.WriteLine("Writing using IEnumerable<MyObject>...");
fastExcel.Add(worksheet, "sheet3");
}
Console.WriteLine(string.Format("Writing IEnumerable<MyObject> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}*/
#region Write Demos
private void FastExcelWriteDemo(FileInfo templateFile, FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 1");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFile))
{
Worksheet worksheet = new Worksheet();
List<Row> rows = new List<Row>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
List<Cell> cells = new List<Cell>();
cells.Add(new Cell(1, 1 * DateTime.Now.Millisecond));
cells.Add(new Cell(2, 2 * DateTime.Now.Millisecond));
cells.Add(new Cell(3, 3 * DateTime.Now.Millisecond));
cells.Add(new Cell(4, 4 * DateTime.Now.Millisecond));
cells.Add(new Cell(5, 45678854));
cells.Add(new Cell(6, 87.01d));
cells.Add(new Cell(7, "Test 1 " + rowNumber));
cells.Add(new Cell(8, DateTime.Now.ToLongTimeString()));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
stopwatch.Start();
Console.WriteLine("Writing data...");
fastExcel.Write(worksheet, "sheet1");
}
Console.WriteLine(string.Format("Writing data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWriteGenericsDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 2");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
List<GenericObject> objectList = new List<GenericObject>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber++)
{
GenericObject genericObject = new GenericObject();
genericObject.IntegerColumn1 = 1 * DateTime.Now.Millisecond;
genericObject.IntegerColumn2 = 2 * DateTime.Now.Millisecond;
genericObject.IntegerColumn3 = 3 * DateTime.Now.Millisecond;
genericObject.IntegerColumn4 = 4 * DateTime.Now.Millisecond;
genericObject.IntegerColumn5 = 45678854;
genericObject.DoubleColumn6 = 87.01d;
genericObject.StringColumn7 = "Test 3" + rowNumber;
genericObject.ObjectColumn8 = DateTime.Now.ToLongTimeString();
objectList.Add(genericObject);
}
stopwatch.Start();
Console.WriteLine("Writing using IEnumerable<MyObject>...");
fastExcel.Write(objectList, "sheet3", true);
}
Console.WriteLine(string.Format("Writing IEnumerable<MyObject> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelWrite2DimensionArrayDemo(FileInfo outputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO WRITE 4");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(outputFile))
{
List<object[]> rowData = new List<object[]>();
// Note rowNumber starts at 2 because we are using existing headings on the sheet
for (int rowNumber = 2; rowNumber < NumberOfRecords; rowNumber++)
{
rowData.Add( new object[]{ 1 * DateTime.Now.Millisecond
, 2 * DateTime.Now.Millisecond
, 3 * DateTime.Now.Millisecond
, 4 * DateTime.Now.Millisecond
, 45678854
, 87.01d
, "Test 2" + rowNumber
, DateTime.Now.ToLongTimeString()});
}
stopwatch.Start();
Console.WriteLine("Writing using IEnumerable<IEnumerable<object>>...");
// Note existingHeadingRows = 1, because we are keeping existing headings on the sheet
fastExcel.Write(rowData, "sheet2", 1);
}
Console.WriteLine(string.Format("Writing using IEnumerable<IEnumerable<object>> took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelReadDemo2(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO READ 2");
Stopwatch stopwatch = Stopwatch.StartNew();
// Open excel file using read/write is slower, but you can also perform writes
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, false))
{
Console.WriteLine("Reading data (Read/Write Access) still needs enumerating...");
Worksheet worksheet = fastExcel.Read("sheet1", 1);
}
Console.WriteLine(string.Format("Reading data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private bool GetWorksheetPropertiesAndValidateNewName(FastExcel fastExcel, int?sheetNumber = null, string sheetName = null, string newSheetName = null)
{
bool newSheetNameExists = false;
//If index has already been loaded then we can skip this function
if (this.Index != 0)
{
return(true);
}
if (!sheetNumber.HasValue && string.IsNullOrEmpty(sheetName))
{
throw new Exception("No worksheet name or number was specified");
}
using (Stream stream = fastExcel.Archive.GetEntry("xl/workbook.xml").Open())
{
XDocument document = XDocument.Load(stream);
if (document == null)
{
throw new Exception("Unable to load workbook.xml");
}
List <XElement> sheetsElements = document.Descendants().Where(d => d.Name.LocalName == "sheet").ToList();
XElement sheetElement = null;
if (sheetNumber.HasValue)
{
if (sheetNumber.Value <= sheetsElements.Count)
{
sheetElement = sheetsElements[sheetNumber.Value - 1];
}
else
{
throw new Exception(string.Format("There is no sheet at index '{0}'", sheetNumber));
}
}
else if (!string.IsNullOrEmpty(sheetName))
{
sheetElement = (from sheet in sheetsElements
from attribute in sheet.Attributes()
where attribute.Name == "name" && attribute.Value.Equals(sheetName, StringComparison.InvariantCultureIgnoreCase)
select sheet).FirstOrDefault();
if (sheetElement == null)
{
throw new Exception(string.Format("There is no sheet named '{0}'", sheetName));
}
if (!string.IsNullOrEmpty(newSheetName))
{
newSheetNameExists = (from sheet in sheetsElements
from attribute in sheet.Attributes()
where attribute.Name == "name" && attribute.Value.Equals(newSheetName, StringComparison.InvariantCultureIgnoreCase)
select sheet).Any();
if (fastExcel.MaxSheetNumber == 0)
{
fastExcel.MaxSheetNumber = (from sheet in sheetsElements
from attribute in sheet.Attributes()
where attribute.Name == "sheetId"
select int.Parse(attribute.Value)).Max();
}
}
}
this.Index = (from attribute in sheetElement.Attributes()
where attribute.Name == "sheetId"
select int.Parse(attribute.Value)).FirstOrDefault();
this.Name = (from attribute in sheetElement.Attributes()
where attribute.Name == "name"
select attribute.Value).FirstOrDefault();
}
if (!this.Exists)
{
throw new Exception("No worksheet was found with the name or number was specified");
}
if (string.IsNullOrEmpty(newSheetName))
{
return(false);
}
else
{
return(!newSheetNameExists);
}
}
/// <summary>
/// Get worksheet file name from xl/workbook.xml
/// </summary>
internal void GetWorksheetProperties(FastExcel fastExcel, int?sheetNumber = null, string sheetName = null)
{
GetWorksheetPropertiesAndValidateNewName(fastExcel, sheetNumber, sheetName);
}
/// <summary>
/// Constructor
/// </summary>
public Worksheet(FastExcel fastExcel)
{
FastExcel = fastExcel;
}
/// <summary>
/// Excel Version
/// </summary>
/// <param name="wr"></param>
/// <param name="ListData"></param>
private void CreateExcelFile(string fileName, List <BaseEntity> ListData)
{
FileInfo outputFile = new FileInfo(fileName);
FileInfo templateFile = new FileInfo("Template.xlsx");
// Read Lisr Properties
List <PropertyInfo> ListProperties = new GwinPropertiesManager()
.GetPropertiesShowenInEntryForm(this.EntityBLO.TypeEntity);
if (outputFile.Exists)
{
outputFile.Delete();
outputFile = new FileInfo(fileName);
}
Console.WriteLine();
Console.WriteLine("DEMO WRITE 1");
// Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFile))
{
Worksheet worksheet = new Worksheet();
List <Row> rows = new List <Row>();
int RowNumber = 1;
// Write Titles
int columnNumber = 1;
List <Cell> cells = new List <Cell>();
foreach (var item in ListProperties)
{
ConfigProperty configProperty = new ConfigProperty(item, this.EntityBLO.ConfigEntity);
cells.Add(new Cell(columnNumber, configProperty.DisplayProperty.Title.ToUpper()));
columnNumber++;
}
rows.Add(new Row(RowNumber, cells));
// Write List Data
RowNumber++;
foreach (BaseEntity itemEntity in ListData)
{
cells = new List <Cell>();
columnNumber = 1;
foreach (var itemProperty in ListProperties)
{
// if Property is Simple type or ManyToOne type
if (itemProperty.PropertyType.Name != "List`1")
{
if (itemProperty.GetValue(itemEntity) != null)
{
Cell cell = new Cell(columnNumber, itemProperty.GetValue(itemEntity).ToString());
cells.Add(cell);
}
else
{
cells.Add(new Cell(columnNumber, ""));
}
}
else
{
// Many To Many Cell
if (itemProperty.GetValue(itemEntity) != null)
{
IList liste_ManytoManyValue = (IList)itemProperty.GetValue(itemEntity);
if (liste_ManytoManyValue != null)
{
string ManyToManyDataString = "";
foreach (var item in liste_ManytoManyValue)
{
ManyToManyDataString += item + " - \r\n";
}
Cell cell = new Cell(columnNumber, ManyToManyDataString);
cells.Add(cell);
}
else
{
Cell cell = new Cell(columnNumber, "");
cells.Add(cell);
}
// Save ManyToMany List to anther Sheet
}
else
{
cells.Add(new Cell(columnNumber, ""));
}
}
// if Property id List : ManyToMany
columnNumber++;
}
rows.Add(new Row(RowNumber, cells));
RowNumber++;
}
worksheet.Rows = rows;
fastExcel.Write(worksheet, "Data");
}
}
/// <summary>
/// Read Data From Excel File
/// </summary>
protected void ReadData()
{
FileInfo inputFile = new FileInfo(this.FileFullPath);
// Test if File Exit
if (!inputFile.Exists)
{
throw new GwinFileNotExistException("The file " + this.FileFullPath + " not exist");
}
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
{
Console.WriteLine("Reading data (Read Only Access) still needs enumerating...");
Worksheet worksheet = fastExcel.Read(1, 1);
// Cleat List of Entities
ListEntities.Clear();
// Read Rows
worksheet.Read();
Row[] rows = worksheet.Rows.ToArray();
// Read PropertyInfo
PropertyInfo[] Properties = typeof(T).GetProperties();
// Create Entity for each Row
for (int i = 1; i < rows.Count(); i++)
{
Row row = rows[i];
// Read Values,
object[] values = new object[Properties.Count()];
foreach (Cell cell in row.Cells)
{
// Excel Cell with empty Data Not exist in Cells
if (cell.ColumnNumber <= Properties.Count())
{
values[cell.ColumnNumber - 1] = cell.Value;
}
}
// Create Entity Instance
T Entity = Activator.CreateInstance <T>();
// Write Value in Entity
int index_column = 0;
foreach (PropertyInfo item in Properties)
{
object value = values[index_column];
try
{
if (value != null)
{
// Fix DateTime convert from Excel to C#
if (item.PropertyType == typeof(DateTime))
{
value = FromExcelSerialDate(Convert.ToInt32(value));
}
item.SetValue(Entity, Convert.ChangeType(value, item.PropertyType));
}
}
catch (Exception e)
{
string message = "Can not read the value " + value + "as " + item.PropertyType;
Entity.AddError(BaseEntity.CategoryError.ExcelFileError, message);
}
index_column++;
}
ListEntities.Add(Entity);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (!(lblInput.Text == "" && lblOutput.Text == ""))
{
var templateFile = new FileInfo(@".\Template.xlsx");
var outputFile = new FileInfo(sSelectedPath + "\\output.xlsx");
var arlist = new ArrayList();
var lineCount = 0;
string fileName = sFileName;
//Create a worksheet with some rows
var worksheet = new Worksheet();
var rows = new List <Row>();
// List<Cell> cells = new List<Cell>();
using (StreamReader reader = new StreamReader(fileName))
{
string line;
while ((line = reader.ReadLine()) != null)
{
lineCount++;
if (lineCount < 89)
{
//Ignore the first 89 Lines
}
else
{
int index = line.IndexOf(@"(");
int index2 = line.LastIndexOf(@")");
try
{
if (line.Contains("re S") && line.Contains("Td"))
{
arlist.Add(line.Substring(index + 1, (index2 - index) - 1));
}
else if (line.Contains("re S"))
{
arlist.Add("");
}
}
catch (Exception a)
{
Console.WriteLine("SKIP");
eventLog.WriteEntry(a.ToString(), EventLogEntryType.Error, 101, 1);
}
}
}
}
Console.WriteLine("Total arraycount: " + arlist.Count);
Console.WriteLine("Total Lines to write: " + arlist.Count / 8);
Console.WriteLine("ARRAYLIST: " + arlist.Count);
int Original_ArrayCount = arlist.Count;
for (int rowNumber = 1; rowNumber < ((Original_ArrayCount / 8) + 1); rowNumber++)
{
List <Cell> cells = new List <Cell>();
for (int counter = 1; counter < 9; counter++)
{
if (arlist.Count == 0)
{
cells.Add(new Cell(counter, "END"));
}
else
{
//Add the first item into the cell of the worksheet and pop it from the list.
//The next in the array will be the first [0] and so on and so fourth
//FILO concept.
cells.Add(new Cell(counter, arlist[0]));
arlist.RemoveAt(0);
}
}
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
// Create an instance of FastExcel
if (File.Exists(sSelectedPath + "\\output.xlsx"))
{
File.Delete(sSelectedPath + "\\output.xlsx");
}
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFile))
{
// Write the data
fastExcel.Write(worksheet, "Template");
}
Console.WriteLine(arlist.Count);
MessageBox.Show("File has been converted successfully. Please exit application.");
}
else
{
MessageBox.Show("Select input and output files.");
}
}
private void FastExcelMergeDemo(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO UPDATE 1");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile))
{
Worksheet worksheet = new Worksheet();
List <Row> rows = new List <Row>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber += 50)
{
List <Cell> cells = new List <Cell>();
for (int columnNumber = 1; columnNumber < 12; columnNumber += 2)
{
cells.Add(new Cell(columnNumber, rowNumber));
}
cells.Add(new Cell(13, "Updated Row"));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
stopwatch.Start();
Console.WriteLine("Updating data every 50th row...");
fastExcel.Update(worksheet, "sheet1");
}
Console.WriteLine(string.Format("Updating data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
private void FastExcelMergeDemo(FileInfo inputFile)
{
Console.WriteLine();
Console.WriteLine("DEMO UPDATE 1");
Stopwatch stopwatch = new Stopwatch();
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile))
{
Worksheet worksheet = new Worksheet();
List<Row> rows = new List<Row>();
for (int rowNumber = 1; rowNumber < NumberOfRecords; rowNumber += 50)
{
List<Cell> cells = new List<Cell>();
for (int columnNumber = 1; columnNumber < 12; columnNumber += 2)
{
cells.Add(new Cell(columnNumber, rowNumber));
}
cells.Add(new Cell(13, "Updated Row"));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
stopwatch.Start();
Console.WriteLine("Updating data every 50th row...");
fastExcel.Update(worksheet, "sheet1");
}
Console.WriteLine(string.Format("Updating data took {0} seconds", stopwatch.Elapsed.TotalSeconds));
}
