how to create excel sheet file using NPOI dll in C# asp.net

Description:-

In the example we explain that how to create excel sheet file using NPOI dll in C# asp.net or how to export data to excel sheet file using NPOI in C#.

There is a multiple way to export data in excel sheet file here we use NPOI dll to easily create or export data to excel sheet file.

You can easily format each cell of the excel sheet file save the file give the name of the excel sheet tab and add multiple sheet in current excel file using NPOI dll.

So here explain how to export data to excel sheet file in C# asp.net using NPOI dll.

You have to download NPOI dll and assign reference to the project in bin folder. And use the namespace like.

Code:-

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using NPOI.SS.Util;

using NPOI.HSSF.Util;

using NPOI.POIFS.FileSystem;

using NPOI.HPSF;

using System.IO;

var workbook = new HSSFWorkbook();

                        var sheet = workbook.CreateSheet("Products List");

                        sheet.SetColumnWidth(0, 20 * 256);

                        sheet.SetColumnWidth(1, 20 * 256);

                        sheet.SetColumnWidth(2, 10 * 256);

                        sheet.SetColumnWidth(3, 20 * 256);

                        sheet.SetColumnWidth(4, 20 * 256);

                        sheet.SetColumnWidth(5, 20 * 256);

                        sheet.SetColumnWidth(6, 20 * 256);

                        sheet.SetColumnWidth(7, 20 * 256);

                        sheet.SetColumnWidth(8, 20 * 256);

                        sheet.SetColumnWidth(9, 20 * 256);

                        sheet.SetColumnWidth(10, 20 * 256);

                        sheet.SetColumnWidth(11, 20 * 256);

                        sheet.SetColumnWidth(12, 20 * 256);

                        sheet.SetColumnWidth(13, 20 * 256);

                        sheet.SetColumnWidth(14, 20 * 256);

                        sheet.SetColumnWidth(15, 20 * 256);

                        sheet.SetColumnWidth(16, 20 * 256);

                        sheet.SetColumnWidth(17, 20 * 256);

                        // Add header labels

                        var rowIndex = 0;

                        var row = sheet.CreateRow(rowIndex);

                        var headerLabelCellStyle = workbook.CreateCellStyle();

                        headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;

                        headerLabelCellStyle.BorderBottom = CellBorderType.THIN;

                        headerLabelCellStyle.BorderLeft = CellBorderType.THIN;

                        headerLabelCellStyle.BorderRight = CellBorderType.THIN;

                        headerLabelCellStyle.BorderTop = CellBorderType.THIN;

                        var headerLabelFont = workbook.CreateFont();

                        headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;

                        headerLabelCellStyle.SetFont(headerLabelFont);

                        headerLabelCellStyle.FillForegroundColor = IndexedColors.BLUE_GREY.Index;

                        headerLabelCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

                        headerLabelCellStyle.WrapText = true;

                        row.CreateCell(0).SetCellValue("1");

                        row.GetCell(0).CellStyle = headerLabelCellStyle;

                        row.CreateCell(1).SetCellValue("2");

                        row.GetCell(1).CellStyle = headerLabelCellStyle;

                        row.CreateCell(2).SetCellValue("3");

                        row.GetCell(2).CellStyle = headerLabelCellStyle;

                        row.CreateCell(3).SetCellValue("4");

                        row.GetCell(3).CellStyle = headerLabelCellStyle;

                        row.CreateCell(4).SetCellValue("5");

                        row.GetCell(4).CellStyle = headerLabelCellStyle;

                        row.CreateCell(5).SetCellValue("6");

                        row.GetCell(5).CellStyle = headerLabelCellStyle;

                        row.CreateCell(6).SetCellValue("7");

                        row.GetCell(6).CellStyle = headerLabelCellStyle;

                        row.CreateCell(7).SetCellValue("8");

                        row.GetCell(7).CellStyle = headerLabelCellStyle;

                        row.CreateCell(8).SetCellValue("9");

                        row.GetCell(8).CellStyle = headerLabelCellStyle;

                        row.CreateCell(9).SetCellValue("10");

                        row.GetCell(9).CellStyle = headerLabelCellStyle;

                        row.CreateCell(10).SetCellValue("11");

                        row.GetCell(10).CellStyle = headerLabelCellStyle;

                        row.CreateCell(11).SetCellValue("12");

                        row.GetCell(11).CellStyle = headerLabelCellStyle;

                        row.CreateCell(12).SetCellValue("13");

                        row.GetCell(12).CellStyle = headerLabelCellStyle;

                        row.CreateCell(13).SetCellValue("14");

                        row.GetCell(13).CellStyle = headerLabelCellStyle;

                        row.CreateCell(14).SetCellValue("15");

                        row.GetCell(14).CellStyle = headerLabelCellStyle;

                        row.CreateCell(15).SetCellValue("16");

                        row.GetCell(15).CellStyle = headerLabelCellStyle;

                        row.CreateCell(16).SetCellValue("17");

                        row.GetCell(16).CellStyle = headerLabelCellStyle;

                        rowIndex++;

                        row = sheet.CreateRow(rowIndex);

                        var headerLabelCellStyle1 = workbook.CreateCellStyle();

                        headerLabelCellStyle1.Alignment = HorizontalAlignment.CENTER;

                        headerLabelCellStyle1.BorderBottom = CellBorderType.THIN;

                        headerLabelCellStyle1.BorderLeft = CellBorderType.THIN;

                        headerLabelCellStyle1.BorderRight = CellBorderType.THIN;

                        headerLabelCellStyle1.BorderTop = CellBorderType.THIN;

                        var headerLabelFont1 = workbook.CreateFont();

                        headerLabelFont1.Boldweight = (short)FontBoldWeight.BOLD;

                        headerLabelCellStyle1.SetFont(headerLabelFont);

                        headerLabelCellStyle1.FillForegroundColor = IndexedColors.YELLOW.Index;

                        headerLabelCellStyle1.FillPattern = FillPatternType.SOLID_FOREGROUND;

                        headerLabelCellStyle1.WrapText = true;

                        row.CreateCell(0).SetCellValue("Contract Line Item Number (CLIN)");

                        row.GetCell(0).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(1).SetCellValue("Category/Group");

                        row.GetCell(1).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(2).SetCellValue("Product Title");

                        row.GetCell(2).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(3).SetCellValue("Original Equipment Manufacturer(OEM)");

                        row.GetCell(3).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(4).SetCellValue("OEM Part #/License #/ID #");

                        row.GetCell(4).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(5).SetCellValue("Product Specification");

                        row.GetCell(5).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(6).SetCellValue("Unit of Measure");

                        row.GetCell(6).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(7).SetCellValue("Catalog Price");

                        row.GetCell(7).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(8).SetCellValue("Discount Percentage from Catalog Price");

                        row.GetCell(8).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(9).SetCellValue("CIO-CS Price");

                        row.GetCell(9).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(10).SetCellValue("GSA Schedule Price");

                        row.GetCell(10).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(11).SetCellValue("TAA Compliance");

                        row.GetCell(11).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(12).SetCellValue("Energy Star Certified");

                        row.GetCell(12).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(13).SetCellValue("EPEAT Compliance");

                        row.GetCell(13).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(14).SetCellValue("Country of Origin");

                        row.GetCell(14).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(15).SetCellValue("Authorized Reseller");

                        row.GetCell(15).CellStyle = headerLabelCellStyle1;

                        row.CreateCell(16).SetCellValue("CLIN Action");

                        row.GetCell(16).CellStyle = headerLabelCellStyle1;

                        rowIndex++;

                        foreach (Telerik.Web.UI.GridDataItem item1 in grdPreApprovedProd.SelectedItems)

                        {

                            string strKey1 = Convert.ToString(item1.GetDataKeyValue("pkc_CIOCS_PreApproveID"));

                            Label lblPartNo1 = (Label)item1.FindControl("lblPartNo");

                            DataTable dtDetails1 = BLPreAprvGSAProduct.GetPreApprovedCIOCSProductById(Convert.ToInt32(strKey1));

                            row = sheet.CreateRow(rowIndex);

                            var headerLabelCellStyle2 = workbook.CreateCellStyle();

                            headerLabelCellStyle2.Alignment = HorizontalAlignment.CENTER;

                            headerLabelCellStyle2.BorderBottom = CellBorderType.THIN;

                            headerLabelCellStyle2.BorderLeft = CellBorderType.THIN;

                            headerLabelCellStyle2.BorderRight = CellBorderType.THIN;

                            headerLabelCellStyle2.BorderTop = CellBorderType.THIN;

                            headerLabelCellStyle2.WrapText = true;

                            DLPreAprvGSAProduct objUpdateProd = new DLPreAprvGSAProduct();

                            objUpdateProd.PreApproveId = Convert.ToInt32(strKey1);

                            objUpdateProd.TranscationNo = id;

                            BLPreAprvGSAProduct.UpdatePreApprovedCIOCSTransIdProduct(objUpdateProd);

                            row.CreateCell(0).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Partno1"]));

                            row.GetCell(0).CellStyle = headerLabelCellStyle2;

                            DataTable dtclass = BLContractCatalog.GetSCLASSByName(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Class"]));

                            if (dtclass != null && dtclass.Rows.Count > 0)

                                row.CreateCell(1).SetCellValue(Convert.ToString(dtclass.Rows[0]["description"]));

                            else

                                row.CreateCell(1).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Class"]));

                            row.GetCell(1).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(2).SetCellValue(Regex.Replace(Convert.ToString(dtDetails1.Rows[0]["CIOCS_SDesc"]), "<(.|\n)*?>", "").Replace("[", "").Replace("]", ""));

                            row.GetCell(2).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(3).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Manf"]));

                            row.GetCell(3).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(4).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Partno1"]));

                            row.GetCell(4).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(5).SetCellValue(Regex.Replace(Convert.ToString(dtDetails1.Rows[0]["CIOCS_SDesc"]), "<(.|\n)*?>", "").Replace("[", "").Replace("]", ""));

                            row.GetCell(5).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(6).SetCellValue("EACH");

                            row.GetCell(6).CellStyle = headerLabelCellStyle2;

                            WebHelper wh1 = new WebHelper();

                            double msrp = 0;

                            double disti = 0;

                            double discount = wh1.GetDiscountByClass(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Class"]));

                            try

                            {

                                if (Convert.ToString(dtDetails1.Rows[0]["CIOCS_MSRP"]).Equals("0") || Convert.ToString(dtDetails1.Rows[0]["CIOCS_MSRP"]).Equals("0.00"))

                                {

                                    msrp = 1;

                                }

                                else

                                {

                                    msrp = Convert.ToDouble(dtDetails1.Rows[0]["CIOCS_MSRP"]);

                                }

                            }

                            catch (Exception ex)

                            {

                                msrp = 1;

                            }

                            try

                            {

                                if (Convert.ToString(dtDetails1.Rows[0]["disticost"]).Equals("0") || Convert.ToString(dtDetails1.Rows[0]["disticost"]).Equals("0.00"))

                                {

                                    disti = 1;

                                }

                                else

                                {

                                    disti = Convert.ToDouble(dtDetails1.Rows[0]["disticost"]);

                                }

                            }

                            catch (Exception ex)

                            {

                                disti = 1;

                            }

                            double RD = (msrp - disti) / msrp * 100;

                            int markup = 8;

                            double markupdic = discount + markup;

                            double diffmarkup = (discount - RD) + markup + discount;

                            if (RD > discount)

                            {

                              

                                double cscatalogprice = msrp;

                                cscatalogprice = Math.Round(cscatalogprice, 2);

                                row.CreateCell(7).SetCellValue(Convert.ToString(cscatalogprice));

                                row.GetCell(7).CellStyle = headerLabelCellStyle2;

                            }

                            else

                            {

                                double cscatalogprice = disti + (disti * diffmarkup) / 100;

                                cscatalogprice = Math.Round(cscatalogprice, 2);

                                row.CreateCell(7).SetCellValue(Convert.ToString(cscatalogprice));

                                row.GetCell(7).CellStyle = headerLabelCellStyle2;

                            }

                            //row.CreateCell(7).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_MSRP"]));

                            //row.GetCell(7).CellStyle = headerLabelCellStyle2;

                            int discount1 = wh.GetDiscountByClass(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Class"]));

                            row.CreateCell(8).SetCellValue(discount1);//discount

                            row.GetCell(8).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(9).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CIOCS_SEWPPRICE"]));

                            row.GetCell(9).CellStyle = headerLabelCellStyle2;

                            DataTable dtgsa = BLPreAprvGSAProduct.GetNTSGSAPriceListByPartNo(Convert.ToString(dtDetails1.Rows[0]["CIOCS_Partno1"]));

                            string gsascprice = string.Empty;

                            if (dtgsa != null && dtgsa.Rows.Count > 0)

                                gsascprice = (!string.IsNullOrEmpty(Convert.ToString(dtgsa.Rows[0]["IFF_p"])) ? Convert.ToString(dtgsa.Rows[0]["IFF_p"]) : string.Empty);

                            row.CreateCell(10).SetCellValue(gsascprice); //gsa sch

                            row.GetCell(10).CellStyle = headerLabelCellStyle2;

                            string TAA = Convert.ToString(dtDetails1.Rows[0]["CIOCS_TAA"]);

                            if (Convert.ToString(dtDetails1.Rows[0]["CIOCS_TAA"]).Equals("NA"))

                                TAA = "N/A";

                            row.CreateCell(11).SetCellValue(TAA);

                            row.GetCell(11).CellStyle = headerLabelCellStyle2;

                            string energy = Convert.ToString(dtDetails1.Rows[0]["EnergyStarCertified"]);

                            if (Convert.ToString(dtDetails1.Rows[0]["EnergyStarCertified"]).Equals("NA"))

                                energy = "N/A";

                            row.CreateCell(12).SetCellValue(energy);

                            row.GetCell(12).CellStyle = headerLabelCellStyle2;

                            string epeat = Convert.ToString(dtDetails1.Rows[0]["EPEATRate"]);

                            if (Convert.ToString(dtDetails1.Rows[0]["EPEATRate"]).Equals("NA"))

                                epeat = "N/A";

                            row.CreateCell(13).SetCellValue(epeat);

                            row.GetCell(13).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(14).SetCellValue(Convert.ToString(dtDetails1.Rows[0]["CountryofOrigin"]));

                            row.GetCell(14).CellStyle = headerLabelCellStyle2;

                            string authorized = Convert.ToString(dtDetails1.Rows[0]["AuthorizedtoSell"]);

                            if (Convert.ToString(dtDetails1.Rows[0]["AuthorizedtoSell"]).Equals("NA"))

                                authorized = "N/A";

                            row.CreateCell(15).SetCellValue(authorized);

                            row.GetCell(15).CellStyle = headerLabelCellStyle2;

                            row.CreateCell(16).SetCellValue("Add");

                            row.GetCell(16).CellStyle = headerLabelCellStyle2;

                            rowIndex++;

                            count++;

                        }

                        // Save the Excel spreadsheet to a file on the web server's file system

                        using (var fileData = new FileStream(Server.MapPath("Files\\" + "TRNTS-" + DateTime.Now.ToString("yyyyMMdd") + "_" + id + ".xls"), FileMode.Create))

                        {

                            workbook.Write(fileData);

                        }