I found myself needing to quickly manipulate some data in an excel sheet.
I had no access to the full blown Visual Studio, no problems I thought, let's see what VSCode, the command line and .NetCore 2 can do.
New project:
no problem
Snideness aside it really is easy - just think of a project folder name...
- dotnet new console
- dotnet restore
- dotnet run
C# 7 has arrived... you no longer need to write this kind of madness:
static Main(string[] args) {
thingIamDoing.RunAsync().GetAwaiter().GetResult();
}
instead:
public static async Task Main(string[] args)
{
await thingIamDoing();
}
pleasing... but a
caveat - you must specify
LangVersion in the csproj as latest to use this:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp2.0</TargetFramework>
<LangVersion>latest</LangVersion>
</PropertyGroup>
</Project>
nuget is yesterdays news... stop using it along with package.config.
instead:
dotnet add package EPPlus.Core
also pleasing... especially as it automatically adds the reference to the csproj:
<ItemGroup>
<PackageReference Include="EPPlus.Core" Version="1.5.4" />
</ItemGroup>
</Project>
so long package.config
So now I have a library to help me read an xlsx file we can experiment. Can this library tell me how many columns and rows there are?
using System.IO;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
...
var sFileName = @"test_file.xlsx";
FileInfo file = new FileInfo(Path.Combine("C:\\", sFileName));
var sb = new StringBuilder();
try
{
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[4];
var rowCount = worksheet.Dimension.Rows;
var colCount = worksheet.Dimension.Columns;
sb.Append($"{colCount} rows: {rowCount}");
}
} catch (Exception ex) {
sb.Append($"An error occurred while importing. {ex.Message}");
}
Console.WriteLine(sb.ToString());
This gives me a result of 13 and 55 rows, which is what I expected.
Interesting to note:
- The API is not zero based, in the example test_file, there really are 4 worksheets
- If the excel spreadsheet is open, expect a file IO/lock error
Now I know the number of columns and rows we can simply iterate through the spreadsheet to read the data. e.g.
var headerPrefix = "headerPrefix_";
if (hasHeader) {
headerPrefix = worksheet.Cells[1, col].Value.ToString();
}
for (int row = hasHeader ? 2 : 1; row <= rowCount; row++)
{
if (worksheet.Cells[row, col].Value == null) continue;
var newValue = worksheet.Cells[row, col].Value.ToString();
To export:
string fileName = @"results.xlsx";
var exportfile = new FileInfo(Path.Combine("C:\\Code\\", fileName));
if (exportfile.Exists)
{
exportfile.Delete();
exportfile = new FileInfo(Path.Combine("C:\\Code\\", fileName));
}
using (ExcelPackage package = new ExcelPackage(exportfile))
{
var worksheet = package.Workbook.Worksheets.Add("results");
worksheet.Cells[1, 1].Value = "Column 1";
// continue on to fill out row data things here ....
// once complete - save
package.Save();
}
jatkeshave commented on Oct 10, 2017