0

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
A Flynn
  • 47
  • 1
  • 8
  • Have you searched StackOverflow, this looks like your problem: http://stackoverflow.com/questions/193092/c-sharp-how-to-add-excel-worksheet-programatically-office-xp-2003 – csharpfolk Jan 15 '16 at 17:00
  • That example isn't using EPPlus, but thanks! – A Flynn Jan 15 '16 at 17:48
  • Looks fine to me. I created a simple unit test (minus the Ole stuff) and everything works. Maybe post more of your code with how you generated the original excel table that contains Template. – Ernie S Jan 16 '16 at 21:46

0 Answers0