Create Excel WITHOUT using INTEROP in c# window service

Question

Can any1 explain how we can create Excel WITHOUT using INTEROP in c# window service. So that I can apply styles also to the generating excel as I wish.

Rigin

"Create Excel" doesn't mean anything. If you want to create a spreadsheet document then use the OpenXML SDK. — Hans Passant
– Hans Passant, Commented Jul 10, 2014 at 14:11
I need to apply styles, colors to the column as I wish, that options are not available with this ? — Rigin
– Rigin, Commented Jul 11, 2014 at 4:03

alex.pulver · Accepted Answer · 2014-08-12 07:08:46Z

0

You can use one of the Excel libraries. I use this C# Excel library. See also this sample of code: http://www.easyxls.com/manual/FAQ/export-to-excel-in-dot-net.html

You can create both XLS or XLSX documents.

edited Aug 12, 2014 at 7:08

answered Jul 10, 2014 at 14:12

alex.pulver

2,1232 gold badges34 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suresh Kamrushi · Accepted Answer · 2019-06-28 05:43:58Z

You can create excel in windows services like below:

public static void GenerateExcel(DataTable DT, string fullFileName, string rptHeader, string SheetName)
        {
            try
            {                
                var file = new FileInfo(fullFileName);
                string currentFileName = System.IO.Path.GetFileName(fullFileName);
                ExcelPackage excel = new ExcelPackage(file);
                var sheetcreate = excel.Workbook.Worksheets.Add("Sheet1");

                //rptHeader = getCaption(rptHeader);
                char c = 'A';
                c = (char)(((int)c) + DT.Columns.Count - 1);
                //sheetcreate.Cells["A1:" + c+"1"].Value = rptHeader;
                sheetcreate.Cells["A1:D1"].Value = rptHeader;
                sheetcreate.Cells["A1:" + c + "1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                //sheetcreate.Cells["A1:" + c + "1"].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#c6c6c6"));
                sheetcreate.Cells[1, 1, 1, DT.Columns.Count].Merge = true;
                sheetcreate.Cells[1, 1, 1, DT.Columns.Count].Style.Font.Bold = true;

                int col = 0;
                foreach (DataColumn column in DT.Columns)
                {
                    sheetcreate.Cells[2, ++col].Value = column.ColumnName;
                    sheetcreate.Cells[2, col].Style.Font.Bold = true;
                    sheetcreate.Cells[2, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                    sheetcreate.Cells[2, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                if (DT.Rows.Count > 0)
                {
                    int row = 2;
                    for (int eachRow = 0; eachRow < DT.Rows.Count; )    //looping each row
                    {
                        bool havingText = false;
                        for (int eachColumn = 1; eachColumn <= col; eachColumn++)   //looping each column in a row
                        {
                            var eachRowObject = sheetcreate.Cells[row + 1, eachColumn];
                            eachRowObject.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            eachRowObject.Value = DT.Rows[eachRow][(eachColumn - 1)].ToString();
                            if (!havingText)     //checking if 'totoa' in string and setting up 'havingText' variable to color it differently
                                havingText = DT.Rows[eachRow][(eachColumn - 1)].ToString().ToLower().Contains("total");

                            //Making all cell value to left align
                            eachRowObject.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                            //if (CL.isDecimal(DT.Rows[eachRow][(eachColumn - 1)].ToString()))      //if it is number with decimal value make it right align                            
                            //{
                            //    eachRowObject.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                            //}
                            //eachRowObject.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);     // adding border to each cells

                            //if (eachRow % 2 == 0)       //alternatively adding color to each cell.
                            //    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#e0e0e0"));
                            //else
                            //    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ffffff"));
                        }
                        if (havingText) //if any cell data containt 'total' color complete.
                        {
                            for (int eachColumn = 1; eachColumn <= col; eachColumn++)
                            {
                                sheetcreate.Cells[row + 1, eachColumn].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                //sheetcreate.Cells[row + 1, eachColumn].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#86a9ef"));
                            }
                        }
                        eachRow++;
                        row++;
                    }
                    getLog("batch controller: in loop");
                }
                getLog("batch controller: 485");
                sheetcreate.Cells.AutoFitColumns();
                excel.Save();
            }
            catch (Exception e)
            {
                getLog("Error while generating excel=>"+e);
            }            
        }

You can download EPPlus from : https://www.nuget.org/packages/EPPlus/

Collectives™ on Stack Overflow

Create Excel WITHOUT using INTEROP in c# window service

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related