NPOI Excel Workbook Creation From Web Service

npoi.cs

    [WebMethod]
    public void WriteExcelWorkbook(List<DataTable> dataTables, List<string> worksheetNames, string workbookName = "file", bool includeHeaders = true)
    {
        NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
        //create bold font for headers
        var font = wb.CreateFont();
        font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
        
        List<string> usedNames = new List<string>();
        for (var xTable = 0; xTable < dataTables.Count; xTable++)
        {
            var nameMatches = usedNames.Where(a => a == worksheetNames[xTable]).Count();
            
            string rawWorksheetName = worksheetNames[xTable];
            
            string worksheetName = rawWorksheetName;
            
            if (worksheetName.Length > 26)
            {
                worksheetName = worksheetName.Substring(0, 25);
            }
            
            if (nameMatches > 0)
            {
                worksheetName = worksheetName + "(" + (nameMatches + 1) + ")";
            }
            
            var sheet = (NPOI.HSSF.UserModel.HSSFSheet)wb.CreateSheet(worksheetName);
            //sheet.DefaultColumnWidth = 30;
            usedNames.Add(rawWorksheetName);
            if (includeHeaders)
            {
                var xRow = sheet.CreateRow(0);
                for (var iCol = 0; iCol < dataTables[xTable].Columns.Count; iCol++)
                {
                    var cell = xRow.CreateCell(iCol);
                    cell.SetCellValue((string)dataTables[xTable].Columns[iCol].ColumnName.ToString());
                    cell.CellStyle = wb.CreateCellStyle();
                    cell.CellStyle.SetFont(font);
                    
                }
            }

            for (var iRow = 0; iRow < dataTables[xTable].Rows.Count; iRow++)
            {
                var currRow = iRow;
                if (includeHeaders == true)
                {
                    currRow = currRow + 1;
                }
                var xRow = sheet.CreateRow(currRow);
                for (var iCol = 0; iCol < dataTables[xTable].Columns.Count; iCol++)
                {
                    var cell = xRow.CreateCell(iCol);
                    cell.SetCellValue((string)dataTables[xTable].Rows[iRow][iCol].ToString());
                }
            }

            for (var iCol = 0; iCol < dataTables[xTable].Columns.Count; iCol++)
            {
                sheet.AutoSizeColumn(iCol);
                sheet.SetColumnWidth(iCol, sheet.GetColumnWidth(iCol) + 2);
            }
        }
        
        var response = Context.Response;

        response.ContentType = "application/octet-stream";

        response.AppendHeader("Content-Disposition", "attachment; filename=" + workbookName + ".xls");

        System.IO.MemoryStream fs = new System.IO.MemoryStream();

        wb.Write(HttpContext.Current.Response.OutputStream);
        
    }