I've searched but can't seem to find an answer that fits my situation. I'm creating a workbook that needs a new tab for each result from a master query, and I want each tab to use the template. Template works fine but I can't figure out how to use it to create new worksheets.
ExcelWorksheet worksheet = packageInUse.Workbook.Worksheets[1];
worksheet.Name = "Template";
var templateSheet = packageInUse.Workbook.Worksheets["Template"];
int rowNbr = 1;
using (OleDbConnection reportConn = new OleDbConnection(IniReadValue("Main", "reportDbConn")))
{
try
{
//first select divsisions based on NetPercent DESC to get them in the right order PER PAY PERIOD
reportConn.Open();
OleDbCommand divsionQry = new OleDbCommand(
"SELECT " +
"DivSumClient.Division, DivSumClient.Client, DivSumClient.NetPercent, DivSumClient.PeriodEndDate " +
"FROM DivSumClient " +
"WHERE DivSumClient.Client = 'TOTAL' And DivSumClient.PeriodEndDate = ? " +
"GROUP BY " +
"DivSumClient.PeriodEndDate, DivSumClient.Division, DivSumClient.Client, DivSumClient.NetPercent " +
"ORDER BY " +
"DivSumClient.NetPercent DESC "
, reportConn);
divsionQry.Parameters.AddWithValue("?", endDate);
OleDbDataReader reader = divsionQry.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//copy template and start new tab for each division
packageInUse.Workbook.Worksheets.Add(reader["Division"].ToString(), templateSheet);
worksheet = packageInUse.Workbook.Worksheets[reader["Division"].ToString()];
I get an error that object reference is not set to an instance of an object on this line: packageInUse.Workbook.Worksheets.Add(reader["Division"].ToString(), templateSheet);
Here's the code I'm using to load the template. It's pretty straightforward and works fine, but using it to create new tabs in the worksheet is not working.
private void runDivSum()
{
System.IO.MemoryStream outputFileStream = new MemoryStream();
System.IO.MemoryStream templateFilestream = new MemoryStream(System.IO.File.ReadAllBytes(appPath + @"\DivisionSummaryTemplate.xlsx"));
using (ExcelPackage package = new ExcelPackage(outputFileStream, templateFilestream))
{
fillDivisionSummary(startDt, endDt);
writeDivSummary(package, startDt, endDt);
package.Save();
}
// Write content of memory stream into file stream
string fileName = @"c:\temp\DivisionSummary.xlsx";
using (var fs = new System.IO.FileStream(fileName, FileMode.Create, FileAccess.Write))
{
outputFileStream.WriteTo(fs);
}
Process excel = new Process();
excel.StartInfo.FileName = "excel.exe";
excel.StartInfo.Arguments = @"c:\temp\DivisionSummary.xlsx";
excel.Start();
SaveFileToSharepoint(fileName);
}
I figured it out. This is the magical combination. I am loading the template as the first worksheet, I stopped trying to name that sheet and just referenced it directly. At the end of the program I just delete that first sheet from the workbook.
ExcelWorksheet worksheet = packageInUse.Workbook.Worksheets[1];
int rowNbr = 1;
worksheet.PrinterSettings.RepeatRows = new ExcelAddress("1:1");
OleDbDataReader reader = divsionQry.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//copy template and start new tab for each division
rowNbr = 1;
packageInUse.Workbook.Worksheets.Add(reader["Division"].ToString(), packageInUse.Workbook.Worksheets[1]);
worksheet = packageInUse.Workbook.Worksheets[reader["Division"].ToString