Wednesday, April 25, 2012

Report Generation in Excel (.xlsx) - Example

Following is an example fo building an excel sheet through C#. I have taken excel sheet as this been a very rich feature in office with we are building a tabular report. The report generated in excel is really easy to work with. Building an excel required building different objects individually and integrating it. Once done then write data in it. After saving the excel releasing all the resources is a key which we seldom forget.

During this implementation I came across an issue that once the excel get genetated and after closing the excel everythign is fine. But when shutting down the PC pop up came for saving excel which is not present in the desktop. That time realized that the objects of excel created through code are still open and active even though there is no excel instance on desktop. For that at reason end added code to release the resources and calling GC.

This example shows building an Excel (.xlsx) report which will diaplay data from list  List<string[]> ListGeneratedSize; String[] has array size of 7. This example has following aspects:
  • Merging cells
  • Setting cell color
  • Setting cell label
  • Setting cell font
  • Assigning excel worksheet features like autofit etc.
  • Releasing resources at the end

My opinion is that even though it would be bit complicated building a report in excel but this adds a lot of value to the report.

using Excel = Microsoft.Office.Interop.Excel; // Need to add to reference
public void BuildExcelReport()
{
    List ListGeneratedSize = new List();
    // In list added string[] of size 7
    Excel.Application app = null;
    Excel.Workbook workbook = null;
    Excel.Workbooks workbooks = null;
    Excel.Worksheet worksheet = null;
    Excel.Range workSheet_range = null;
    int irow = 1;
    try
    {
        app = new Excel.Application();
        workbooks = app.Workbooks;
        workbook = workbooks.Add(1);
        worksheet = (Excel.Worksheet)workbook.Sheets[1];
        worksheet.Cells[irow, 2] = lZAM1.Text.Substring(0, lZAM1.Text.IndexOf(";"));
        workSheet_range = worksheet.get_Range("B" + irow, "D" + irow);
        workSheet_range.Merge(0);
        workSheet_range.Font.Bold = true;

        worksheet.Cells[irow, 5] = lZAM2.Text.Substring(0, lZAM2.Text.IndexOf(";"));
        workSheet_range = worksheet.get_Range("E" + irow, "G" + irow);
        workSheet_range.Merge(0);
        workSheet_range.Font.Bold = true;
        workSheet_range = worksheet.get_Range("A" + irow, "G" + irow);
        workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
        workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
        workSheet_range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AntiqueWhite);
        irow++;
        worksheet.Cells[irow, 1] = "Label 1";
        worksheet.Cells[irow, 2] = "Label 2";
        worksheet.Cells[irow, 3] = "Label 3";
        worksheet.Cells[irow, 4] = "Label 4";
        worksheet.Cells[irow, 5] = "Label 5";
        worksheet.Cells[irow, 6] = "Label 6";
        worksheet.Cells[irow, 7] = "Label 7";
        workSheet_range = worksheet.get_Range("A" + irow, "G" + irow);
        workSheet_range.Font.Bold = true;
        workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
        irow++;
        if (ListGeneratedSize.Count > 0)
        {
            workSheet_range = worksheet.get_Range("A" + irow, "G" + irow);
            workSheet_range.Merge(0);
            workSheet_range.Font.Bold = true;
            workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
            worksheet.Cells[irow, 1] = "Test Heading";
            irow++;
            foreach (string[] sData in ListGeneratedSize)
            {
                workSheet_range = worksheet.get_Range("A" + irow, "A" + irow);
                workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Azure);
                workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
                workSheet_range = worksheet.get_Range("B" + irow, "D" + irow);
                workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LemonChiffon);
                workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
                workSheet_range = worksheet.get_Range("E" + irow, "G" + irow);
                workSheet_range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Beige);
                workSheet_range.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RosyBrown);
                for (int i = 0; i <= 6; i++)
                {
                    worksheet.Cells[irow, (i + 1)] = sData[i];
                }
                irow++;
            }
        }
        File.Delete(@"C:\Test.xlsx");
        worksheet.Cells.Columns.AutoFit();
        workbook.SaveAs(@"C:\Test.xlsx",
            Excel.XlFileFormat.xlWorkbookNormal,
            Type.Missing,
            Type.Missing,
            Type.Missing,
            Type.Missing,
            Excel.XlSaveAsAccessMode.xlExclusive,
            Type.Missing,
            Type.Missing,
            Type.Missing,
            Type.Missing,
            Type.Missing);
    }
    finally // Releasing all the resources
    {
        if (workbook != null)
            workbook.Close(false, null, null);
        if (workbooks != null)
            workbooks.Close();
        if(app != null)
            app.Quit();
       
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workSheet_range);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbooks);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);
        workSheet_range = null;
        workbooks = null;
        worksheet = null;
        workbook = null;
        app = null;
       
        GC.Collect();
        GC.WaitForPendingFinalizers();

        // Launching excel sheet
        System.Diagnostics.Process p = new System.Diagnostics.Process();
        p.StartInfo.FileName = @"C:\Test.xlsx";
        p.Start();
    }
}

No comments:

Post a Comment