Purpose of this blog

This blog mainly documents creating excel file with multiple worksheet for preparing a consolidated data report . The problems that I document in this blog are those whose solutions or comments I could not find easily with search engine. Therefore, the purpose of this blog is to help people who encounter similar problem.

Requirements

Before start you need to download The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages. you can download it from here or you can download it from   http://www.microsoft.com/downloads/details.aspx?FamilyID=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en you also need give the reference for windowbase Dll in your project

Code Block

public static bool CreateExcelDocument( string excelFilename)

        {

            try

            {

                using (SpreadsheetDocument document =SpreadsheetDocument.Create  (excelFilename, SpreadsheetDocumentType.Workbook))

                {

                    document.AddWorkbookPart();

                    document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

WorkbookStylesPart stylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();

Stylesheet styles = new CustomStylesheet();

styles.Save(stylesPart);

CreateParts( document);

                      }

                Trace.WriteLine("Successfully created: " + excelFilename);

                return true;

            }

            catch (Exception ex)

            {

                Trace.WriteLine("Failed, exception thrown: " + ex.Message);

                return false;

            }

        }

private static void CreateParts(SpreadsheetDocument spreadsheet)

        {

            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.

            uint worksheetNumber = 1;

//ApplicatinNames is a Enum variable through which I am enumerating Constant

            foreach (ApplicationsNames state in EnumToList<ApplicationsNames>())

            {

                string s=GetEnumDescription(state); 

                //Here you can call the function to fill your datatable          

                DataTable dt=CreateTableData(s);

                //  For each worksheet you want to create

                string workSheetID = "rId" + worksheetNumber.ToString();

                

                string worksheetName = dt.TableName;

                WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();

                newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

                // create sheet data              

                

                List<string> listheader = new List<string>();

                Columns columns = new Columns();

                int numCols = dt.Columns.Count + 3;

                int width = 0;

                int col = 3;

                foreach(DataColumn dc in dt.Columns )

                {  

                   

                        width = dc.ColumnName.Length + 7;

                    

                    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    columns.Append(c); col++;

                }

               

                newWorksheetPart.Worksheet.Append(columns);

                newWorksheetPart.Worksheet.AppendChild(CreateSheetData(dt, listheader));               

                newWorksheetPart.Worksheet.Save();

                // create the worksheet to workbook relation

                if (worksheetNumber == 1)

                    spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());                spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()

                {

                    Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),

                    SheetId = (uint)worksheetNumber,

                    Name = dt.TableName

                });
                worksheetNumber++;

            }

            spreadsheet.WorkbookPart.Workbook.Save();

        }

public SheetData CreateSheetData(DataTable objects, List<string> headerNames)

        {

            SheetData sheetData = new SheetData();

            if (objects != null)

            {

                string cellValue = "";

                var az = new List<Char>(Enumerable.Range('B', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());

                List<Char> headers = az.GetRange(0, headerNames.Count);

                int numberOfColumns = objects.Columns.Count;

                bool[] IsNumericColumn = new bool[numberOfColumns];

                Row tableName = new Row();

                uint rowIndex = 2;

                tableName.RowIndex = rowIndex;

                var c1 = new HeaderCell("F", objects.TableName.ToString(), Convert.ToInt32(rowIndex));

                tableName.Append(c1);

                sheetData.Append(tableName);

                string[] excelColumnNames = new string[numberOfColumns];

                for (int n = 0; n < numberOfColumns; n++)

                    excelColumnNames[n] = GetExcelColumnName(n);

                int numRows = objects.Columns.Count;

                int numCols = headerNames.Count;

                Row header = new Row();

                rowIndex = 4;

                header.RowIndex = (uint)rowIndex;

                for (int col = 0; col < numCols; col++)

                {

                    var c = new HeaderCell(headers[col].ToString(),

                                           headerNames[col], Convert.ToInt32(rowIndex));

                    header.Append(c);

                }

                sheetData.Append(header);

                double cellNumericValue = 0;

                foreach (DataRow dr in objects.Rows)

                {

                    // ...create a new row, and append a set of this row's data to it.

                    ++rowIndex;

                    var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet

                    sheetData.Append(newExcelRow);

                    for (int colInx = 0; colInx < numberOfColumns; colInx++)

                    {

                        cellValue = dr.ItemArray[colInx].ToString();

                        if (colInx == 0)

                        {  

                            if (double.TryParse(cellValue, out cellNumericValue))

                            {

                                cellValue = (rowIndex-6).ToString();

                                AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow,colr);

                               

                            }

                        }                       

                        // Create cell with data

                        else if (IsNumericColumn[colInx])

                        {

                            cellNumericValue = 0;

                            if (double.TryParse(cellValue, out cellNumericValue))

                            {

                                cellValue = cellNumericValue.ToString();

                                AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow,colr);

                            }

                        }

                        else

                        {

                            //  For text cells, just write the input data straight out to the Excel file.

                            AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow,colr);

                        }

                    }

                }

            }

            return sheetData;

        }

public Column CreateColumnData(UInt32 startColumnIndex, UInt32 endColumnIndex, double columnWidth)

        {

            Column column;

                        column = new CustomColumn(startColumnIndex,endColumnIndex,columnWidth);

            column.CustomWidth = true;

            return column;

        }

private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow,uint i)

        {

            //  Add a new Excel Cell to our Row

            Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };

            CellValue cellValue = new CellValue();

            cell.StyleIndex = i;

            cellValue.Text = cellStringValue;

            cell.Append(cellValue);

            excelRow.Append(cell);

           

        }

        private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow,uint i)

        {

            //  Add a new Excel Cell to our Row

            Cell cell = new Cell() { CellReference = cellReference };

            CellValue cellValue = new CellValue();

            cell.StyleIndex = i;

            cellValue.Text = cellStringValue;

            cell.Append(cellValue);

            excelRow.Append(cell);           

        }

        private static string GetExcelColumnName(int columnIndex)

        {

            //  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)

            //

            //  eg  GetExcelColumnName(0) should return "A"

            //      GetExcelColumnName(1) should return "B"

            //      GetExcelColumnName(25) should return "Z"

            //      GetExcelColumnName(26) should return "AA"

            if (columnIndex < 26)

                return ((char)('B' + columnIndex)).ToString();

            char firstChar = (char)('A' + (columnIndex / 26) - 1);

            char secondChar = (char)('A' + (columnIndex % 26));

            return string.Format("{0}{1}", firstChar, secondChar);

        }

}

public class HeaderCell : TextCell

    {

        public HeaderCell(string header, string text, int index)

            : base(header, text, index)

        {

            this.StyleIndex = 9;           

           

        }

}

public class TextCell : Cell

    {

        public TextCell(string header, string text, int index)

        {

           

            this.DataType = CellValues.InlineString;

            this.CellReference = header + index;

            //Add text to the text cell.

            this.InlineString = new InlineString { Text = new Text { Text = text } };

        }

    }

public class NumberCell : Cell

    {

        public NumberCell(string header, string text, int index)

        {

            this.DataType = CellValues.Number;

            this.CellReference = header + index;

            this.CellValue = new CellValue(text);

        }

    }

public class CustomColumn : Column

        {

            public CustomColumn(UInt32 startColumnIndex,

                   UInt32 endColumnIndex, double columnWidth)

            {

                this.Min = startColumnIndex;

                this.Max = endColumnIndex;

                this.Width = columnWidth;

                this.CustomWidth = true;

            }

        }

   

public class CustomStylesheet : Stylesheet

    {

        public CustomStylesheet()

        {

            var fonts = new Fonts();

            var font = new DocumentFormat.OpenXml.Spreadsheet.Font();

            var fontName = new FontName { Val = StringValue.FromString("Arial") };

            var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };

            font.FontName = fontName;

            font.FontSize = fontSize;

            fonts.Append(font);

            //Font Index 1

            font = new DocumentFormat.OpenXml.Spreadsheet.Font();

            fontName = new FontName { Val = StringValue.FromString("Arial") };

            fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };

            font.FontName = fontName;

            font.FontSize = fontSize;

            font.Bold = new Bold();

            fonts.Append(font);

            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);

            var fills = new Fills();

            var fill = new Fill();

            var patternFill = new PatternFill { PatternType = PatternValues.None };

            fill.PatternFill = patternFill;

            fills.Append(fill);

            fill = new Fill();

            patternFill = new PatternFill { PatternType = PatternValues.Gray125 };

            fill.PatternFill = patternFill;

            fills.Append(fill);

            //Fill index  2

            fill = new Fill();

            patternFill = new PatternFill

            {

                PatternType = PatternValues.Solid,

                ForegroundColor = new ForegroundColor()

            };

            patternFill.ForegroundColor =

               TranslateForeground(System.Drawing.Color.LightBlue);

            patternFill.BackgroundColor =

                new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };

            fill.PatternFill = patternFill;

            fills.Append(fill);

            //Fill index  3

            fill = new Fill();

            patternFill = new PatternFill

            {

                PatternType = PatternValues.Solid,

                ForegroundColor = new ForegroundColor()

            };

            patternFill.ForegroundColor =

               TranslateForeground(System.Drawing.Color.DodgerBlue);

            patternFill.BackgroundColor =

               new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };

            fill.PatternFill = patternFill;

            fills.Append(fill);

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

            var borders = new Borders();

            var border = new Border

            {

                LeftBorder = new LeftBorder(),

                RightBorder = new RightBorder(),

                TopBorder = new TopBorder(),

                BottomBorder = new BottomBorder(),

                DiagonalBorder = new DiagonalBorder()

            };

            borders.Append(border);

            //All Boarder Index 1

            border = new Border

            {

                LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },

                RightBorder = new RightBorder { Style = BorderStyleValues.Thin },

                TopBorder = new TopBorder { Style = BorderStyleValues.Thin },

                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },

                DiagonalBorder = new DiagonalBorder()

            };

            borders.Append(border);

            //Top and Bottom Boarder Index 2

            border = new Border

            {

                LeftBorder = new LeftBorder(),

                RightBorder = new RightBorder(),

                TopBorder = new TopBorder { Style = BorderStyleValues.Thin },

                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },

                DiagonalBorder = new DiagonalBorder()

            };

            borders.Append(border);

            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

            var cellStyleFormats = new CellStyleFormats();

            var cellFormat = new CellFormat

            {

                NumberFormatId = 0,

                FontId = 0,

                FillId = 0,

                BorderId = 0

            };

            cellStyleFormats.Append(cellFormat);

            cellStyleFormats.Count =

               UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);

            uint iExcelIndex = 164;

            var numberingFormats = new NumberingFormats();

            var cellFormats = new CellFormats();

            cellFormat = new CellFormat

            {

                NumberFormatId = 0,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0

            };

            cellFormats.Append(cellFormat);

            var nformatDateTime = new NumberingFormat

            {

                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),

                FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")

            };

            numberingFormats.Append(nformatDateTime);

            var nformat4Decimal = new NumberingFormat

            {

                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),

                FormatCode = StringValue.FromString("#,##0.0000")

            };

            numberingFormats.Append(nformat4Decimal);

            var nformat2Decimal = new NumberingFormat

            {

                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),

                FormatCode = StringValue.FromString("#,##0.00")

            };

            numberingFormats.Append(nformat2Decimal);

            var nformatForcedText = new NumberingFormat

            {

                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),

                FormatCode = StringValue.FromString("@")

            };

            numberingFormats.Append(nformatForcedText);

            // index 1

            // Cell Standard Date format

            cellFormat = new CellFormat

            {

                NumberFormatId = 14,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 2

            // Cell Standard Number format with 2 decimal placing

            cellFormat = new CellFormat

            {

                NumberFormatId = 4,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 3

            // Cell Date time custom format

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatDateTime.NumberFormatId,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 4

            // Cell 4 decimal custom format

            cellFormat = new CellFormat

            {

                NumberFormatId = nformat4Decimal.NumberFormatId,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 5

            // Cell 2 decimal custom format

            cellFormat = new CellFormat

            {

                NumberFormatId = nformat2Decimal.NumberFormatId,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 6

            // Cell forced number text custom format

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatForcedText.NumberFormatId,

                FontId = 0,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 7

            // Cell text with font 12

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatForcedText.NumberFormatId,

                FontId = 1,

                FillId = 0,

                BorderId = 0,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 8

            // Cell text

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatForcedText.NumberFormatId,

                FontId = 0,

                FillId = 0,

                BorderId = 1,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 9

            // Coloured 2 decimal cell text

            cellFormat = new CellFormat

            {

                NumberFormatId = nformat2Decimal.NumberFormatId,

                FontId = 1,

                FillId = 3,

                BorderId = 2,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 10

            // Coloured cell text

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatForcedText.NumberFormatId,

                FontId = 0,

                FillId = 2,

                BorderId = 2,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            // Index 11

            // Coloured cell text

            cellFormat = new CellFormat

            {

                NumberFormatId = nformatForcedText.NumberFormatId,

                FontId = 1,

                FillId = 3,

                BorderId = 2,

                FormatId = 0,

                ApplyNumberFormat = BooleanValue.FromBoolean(true)

            };

            cellFormats.Append(cellFormat);

            numberingFormats.Count =

              UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);

            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            this.Append(numberingFormats);

            this.Append(fonts);

            this.Append(fills);

            this.Append(borders);

            this.Append(cellStyleFormats);

            this.Append(cellFormats);

            var css = new CellStyles();

            var cs = new CellStyle

            {

                Name = StringValue.FromString("Normal"),

                FormatId = 0,

                BuiltinId = 0

            };

            css.Append(cs);

            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);

            this.Append(css);

            var dfs = new DifferentialFormats { Count = 0 };

            this.Append(dfs);

            var tss = new TableStyles

            {

                Count = 0,

                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),

                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")

            };

            this.Append(tss);

        }

        private static ForegroundColor TranslateForeground(System.Drawing.Color fillColor)

        {

            return new ForegroundColor()

            {

                Rgb = new HexBinaryValue()

                {

                    Value =

                        System.Drawing.ColorTranslator.ToHtml(

                        System.Drawing.Color.FromArgb(

                            fillColor.A,

                            fillColor.R,

                            fillColor.G,

                            fillColor.B)).Replace("#", "")

                }

            };

        }

    }