c#excelnpoi
Npoi: We found a problem with some content (C#)
I am using NPOI to generate excel file from Sql table. Everything works fine but when i tried to open the file it give me following error.
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
namespace SfApi.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class SuccessfactorController : ControllerBase
{
private DataContext _context;
private string sqlstring;
public SuccessfactorController(DataContext context)
{
_context = context;
//sqlstring will be use in each method in this controller with query
sqlstring = _context.Database.GetConnectionString();
}
[HttpGet("GetSfResults")]
public async Task<ActionResult<List<Successfactor>>> Get()
{
DataTable ds = new DataTable();
using (SqlConnection con = new SqlConnection(sqlstring))
{
string query = "SELECT * FROM test";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(ds);
}
}
}
DataTable_To_Excel(ds, "D:\\ExcelTest\\ExcelTest.xlsx");
return Ok(_context.Successfactor);
}
Above code is getting data from Sql and filling it into data table.
private void DataTable_To_Excel(DataTable pDatos, string pFilePath)
{
try
{
if (pDatos != null && pDatos.Rows.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.Write))
{
string Ext = System.IO.Path.GetExtension(pFilePath); //<- File extension
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Saskpolytech"; dsi.Manager = "Learning Technology";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
//worksheet = workbook.CreateSheet(pDatos.TableName); //<- Use table name as Sheet name
worksheet = workbook.CreateSheet("testSheetName"); //<- Use table name as Sheet name
//FORMATS FOR CERTAIN TYPES OF DATA
ICellStyle _TextCellStyle = workbook.CreateCellStyle();
_TextCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("text");
ICellStyle _doubleCellStyle = workbook.CreateCellStyle();
_doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.###");
ICellStyle _intCellStyle = workbook.CreateCellStyle();
_intCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
ICellStyle _boolCellStyle = workbook.CreateCellStyle();
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");
ICellStyle _dateCellStyle = workbook.CreateCellStyle();
_dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
_dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");
//REAR IN THE FIRST ROW THE TITLES OF THE COLUMNS
int iRow = 0;
if (pDatos.Columns.Count > 0)
{
int iCol = 0;
IRow fila = worksheet.CreateRow(iRow);
foreach (DataColumn columna in pDatos.Columns)
{
ICell cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(columna.ColumnName);
cell.CellStyle = _TextCellStyle;
iCol++;
}
iRow++;
}
//NOW CREATE A ROW FOR EACH RECORD IN THE TABLE
foreach (DataRow row in pDatos.Rows)
{
IRow fila = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in pDatos.Columns)
{
ICell cell = null; //<-Represents the current cell
object cellValue = row[iCol]; //<- The current value of the cell
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue)) { cell.SetCellFormula("TRUE()"); }
else { cell.SetCellFormula("FALSE()"); }
cell.CellStyle = _boolCellStyle;
}
break;
case "System.String":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue (Convert.ToString(cellValue));
cell.CellStyle = _TextCellStyle;
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDateTime(cellValue));
//If there is no Time value, use dd-MM-yyyy format
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
cell.CellStyle = _dateTimeCellStyle;
}
else { cell.CellStyle = _dateCellStyle; }
}
break;
default:
break;
}
iCol++;
}
iRow++;
}
workbook.Write(stream);
stream.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
I have put lot of time in finding out answer, I can't find anything on internet. if some one can guide me would be great.
Solution
There is no pre-defined format called "BOOLEAN" so using it in your cellstyle invalidates the Excel Cell.
This line should be changed:
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");
to
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("General");
It is worth mentioning that if you click Yes on that dialog you screenshotted in your question, my Excel version reports what it repaired:
Repaired Records: Format from /xl/styles.xml part (Styles)
which can narrow your search drastically for finding the root cause.
- How to perform addition of two vectors of 8-bit integers with a single addition in C/C++
- GNU RISC-V Embedded GCC throws "x ISA extension `xw' must be set with the versions" error
- How to implement variable sized array within C struct
- Counting pulses using a swiss flow meter with an Arduino, how is it done?
- How to create a folder in C (need to run on both Linux and Windows)
- Is there any way to compute the width of an integer type at compile-time?
- What kind of implementation can I use for a static associative array on a vintage system with very limited resources?
- How can I initialize all members of an array to the same value?
- Is C notably faster than C++
- How to get the Windows SDK version number a program is compiling with at compile time
- Confused by difference between expression inside if and expression outside if
- Equivalent of atoi for unsigned integers
- k&r: Exercise 1-18. Program takes input but doesnt produce any output?
- Using in C thrd_sleep() to either wait for time or interrupt by signal. Example?
- How to get the sign, mantissa and exponent of a floating point number
- How can I compute `exp(x)/2` when `x` is large?
- c programming: answer always equates to 0
- Is it possible to access a parameter of a function from another function in C?
- Will this expression evaluate to true or false (1 or 0) in C?
- What Is the Return Value of strcspn() When Str1 Does not Contain Str2?
- Mapping a numeric range onto another
- Signalled and non-signalled state of event
- Why is faster to do a branch than a lookup?
- Avoid security.ArrayBound warning when using container_of
- Forcing 64-bit long doubles?
- "error: redeclaration of 'myVariable' with no linkage:" - what's the linkage?
- Branchlessly map one list of integers to another
- SQLBindCol for long integer value on 64Bit platform?
- What's wrong with my code? CS50 Pset2: Readability
- Is there a way to redirect stdout/stderr to a string?