Excelerate has been updated for .NET10. Use a 9x version for .NET9, Use an 8.x version for .NET8, a
7.x version for .NET7. All new development is on the .NET 10 version.
Excelerate will change how you work with C# and Excel. Excelerate uses NPOI and it makes
it easy to load Workbooks or Worksheets. Excelerate now has a code generation feature,
you can create a C# class from a header row. Excelerate also has helper classes to allow
you load and save entire workbooks or worksheets without ever having referring to a cell.
Use my free site https://excelerate.datajuggler.com to code generate C# classes from Excel
header rows. ExcelDataLoader will load a workbook by passing in a path or a Worksheet
by passing in a a WorksheetInfo object to specify what to load.
Background
I have a couple of clients that I built programs that automate Excel processes. This
project streamlines Excel development.
7.22.2023: I have completed ExcelHelper.SaveWorksheet method. This is a major milestone
so I have updated the project to 7.4.0.
7.22.2023: I am in the process of redoing SaveRow for the code generated objects. Now each class
has two properties created. Loading, and ChangedColumns, which is a comma delimited string of
column indexes that have chagned. The next phase will be only saving columns that have changes.
7.21.2023: I added a property to the column object called HasChanges. In conjunection with this the class
ExcelHelper has an optional parameter to SaveBatch, SaveBatchItem and SaveRow to only save columns with
HasChanges = true. For now, you have to set this property on the column manually. I am investigating ways to
auto set this if the value changes from the time you loaded the object until you save it.\
7.16.2023: DataJuggler.UltimateHelper, DataJuggler.Net7 was updated.
Update 4.4.2023
I just released a cool project that uses this package.
DataJuggler.SQLSnapshot
Export a SQL Server database and all data rows with just a connection string and the path to save the Excel file.
Nuget DataJuggler.SQLSnapshot
https://github.com/DataJuggler/SQLSnapshot
Update 11.11.2022: I am working on the Grid, and added a properties to the column object
and some code to the ValidationComponent to allow SetFocusOnFirstRender.
Update 11.9.2022 - 11.10.2022:
I added some new properties and enumerations for editing data in the Grid in DataJuggler.Blazor.Components.
Update 11.8.2022:
I added some new properties to the row and Column object for use with the Grid for DataJuggler.Blazor.Components.
Update 10.31.2022:
LoadWorksheetInfo.ExcludedColumnIndexes was added. This is a collection of integers
to not load. I may expand this to column names also as an option.
--
Excelerate uses EPPPlus version 4.5.3.3 (last free version), and it makes it easy to load Workbooks or Worksheets.
A class named CodeGenerator was just created, and now by inheriting from the same CSharpClassWriter that code generates for DataTier.Net, I code generate
classes based on your header row.
I have a couple of clients that I build programs that automate combining columns from multiple Worksheets to form reports.
Rather than continue to write custom loaders, I really only need custom Exporters in most cases.
This short code snippet will load all the rows from a worksheet:
Snippet is from a Windows Form .Net 6 project, located in the Sample folder of this project. Very simple for now:
Load Worksheet Sample
using DataJuggler.UltimateHelper;
using DataJuggler.Excelerate;
using System;
using System.Windows.Forms;
// Set the text
string path = WorksheetControl.Text;
// Create a new instance of a 'LoadWorksheetInfo' object.
LoadWorksheetInfo loadWorksheetInfo = new LoadWorksheetInfo();
// Set the SheetName
oadWorksheetInfo.SheetName = SheetnameControl.SelectedObject.ToString();
// Only load the first 12 columns for this test
loadWorksheetInfo.ColumnsToLoad = 12;
// Set the LoadColumnOptions
loadWorksheetInfo.LoadColumnOptions = LoadColumnOptionsEnum.LoadFirstXColumns;
// other options
// loadWorksheetInfo.LoadColumnOptions = LoadColumnOptionsEnum.LoadAllColumnsExceptExcluded;
// loadWorksheetInfo.LoadColumnOptions = LoadColumnOptionsEnum.LoadSpecifiedColumns;
// load the worksheet
Worksheet worksheet = ExcelDataLoader.LoadWorksheet(path, loadWorksheetInfo);
// if the worksheet exists
if ((NullHelper.Exists(worksheet)) && (SheetnameControl.HasSelectedObject))
{
// if the rows collection was found
if (worksheet.HasRows)
{
// Show a message as a test
// MessageBox.Show("Worksheet Loaded", "Finished");
// test only
// int rows = worksheet.Rows.Count;
// Show a message as a test
// MessageBox.Show("There were " + String.Format("{0:n0}", rows) + " rows found in the worksheet");
// int cols = worksheet.Rows[1124].Columns.Count;
// Show a message as a test
// MessageBox.Show("There were " + String.Format("{0:n0}", cols) + " columns found in the row index 1125.");
// Get a nullable date
// string columnValue = worksheet.Rows[1124].Columns[3].DateValue;
// Show a message of the columnValue
// MessageBox.Show("Column Value: " + columnValue);
}
}
There is now a Code Generator class built into this project, to code generate a C# class from a header row.
The Code Generator has been updated to pass in a Row instance, to make loading the generate classes simple.
This code is from a Windows Form .Net 5 project located in the sample:
# Code Generation Sample
// if the value for HasWorksheet is true
if ((HasWorksheet) && (ListHelper.HasOneOrMoreItems(Worksheet.Rows)))
{
// The file I am using to test has 3 rows at the top above the header row. Take this out if I accidently check this in
// worksheet.Rows.RemoveRange(0, 3);
// Set the outputFolder
string outputFolder = OutputFolderControl.Text;
// Set the className (the name of the generated class)
string className = "SalesTaxEntry";
// Create a new instance of a CodeGenerator
CodeGenerator codeGenerator = new CodeGenerator(worksheet, outputFolder, className);
// Generate a class and set the Namespace
bool success = codeGenerator.GenerateClassFromWorksheet("STATS.Objects");
// Show the results
MessageBox.Show("Success: " + success);
}
There is another override to load multiple sheets at once. I will build a sample project when I get some time to build a sample spreadsheet I can give away.
To load multiple sheets:
List<LoadWorksheetInfo> loadWorkSheetsInfo = new List<LoadWorksheetInfo>();
// Add each LoadWorksheetInfo
workbook = ExcellDataLoader.LoadWorkbook(path, loadWorkSheetsInfo)
I will build some helper methods to save writing as much code once I use this a little to know what is needed.
My first test loaded a 12 column spreadsheet with 3,376 rows in just a few seconds.
I have a new project that uses this project as a good sample. Blazor.Excelerate will soon be an online
way to create classes from a spreadsheet.
More helper methods and features will be added. The Nuget package has been released: DataJuggler.Excelerate.
Feel free to mention any new features you think would be useful. I can't promise to do them all, but if it is a good fit for this project I will add it.
This code is all brand new, so use with caution until more testing has been done. First tests have been promising.
I just finished adding a Load method, that is code generated when the classes are written.
** I am available for hire if you need help with any size C# / SQL Server project **
