Edit existing Excel file C# npoi

Question

I want to with a console application C# open an existing excel file and add content to it. NPOI 2.1.1.0

My first approach was simply to add a value to last cell figure I solved that it will solve my other problem.
This will read the file correctly with the new content but it will not save it.

Output:

"Cannot access a closed file.".

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\testfile.xls", FileMode.Open, FileAccess.ReadWrite))
{
    hssfwb = new HSSFWorkbook(file);
    ISheet sheet = hssfwb.GetSheetAt(0);
    IRow row = sheet.GetRow(0);

    sheet.CreateRow(row.LastCellNum);
    ICell cell = row.CreateCell(row.LastCellNum);
    cell.SetCellValue("test");

    for (int i = 0; i < row.LastCellNum; i++)
    {
        Console.WriteLine(row.GetCell(i));
    }
    hssfwb.Write(file);
    file.Close();
}

I did try add the following but it resulted in a blank file that wont open.

MemoryStream mstream = new MemoryStream();
hssfwb.Write(mstream);

byte[] bytes = new byte[mstream.Length];
mstream.Read(bytes, 0, (int)mstream.Length);
file.Write(bytes, 0, bytes.Length);
file.Close();
mstream.Close();

I also tried putting the hssfwb alone within the using scope, changed the filemode to Append/openorcreate/truncate, added if write check without any result.

However one solution would be to read the file, convert it into an datatable, create a new excel file and populate it with the data.

What am I doing wrong? Can anyone shed some light on this? I'v looked over the Npoi Example package in vain.

Preemie

You can use this solution to edit excel col row with NPOI https://mcmap.net/q/245028/-npoi-setcellvalue-method-doesn-39-t-write-data-to-cells – Kallick

Answer 1

Well, it looks like HSSFWorkbook constructor closes filestream after reading from it.

Simpliest and straightforward solution - open file for reading, create HSSFWorkbook, do what you want in that workbook, and then open file again for writing and write to it.

It is safe to use hssfwb outside of using scope because HSSFWorkbook itself does not holds reference to file from which it was read (as long as I see it in NPOI sources).

So your code could look like:

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Read))
{
    hssfwb = new HSSFWorkbook(file);
    file.Close();
}

ISheet sheet = hssfwb.GetSheetAt(0);
IRow row = sheet.GetRow(0);

sheet.CreateRow(row.LastCellNum);
ICell cell = row.CreateCell(row.LastCellNum);
cell.SetCellValue("test");

for (int i = 0; i < row.LastCellNum; i++)
{
    Console.WriteLine(row.GetCell(i));
}

using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Write))
{
    hssfwb.Write(file);
    file.Close();
}

Lippe

I tried to run the code you posted but it gave me error saying "OfficeXmlFileException" was not handled at line 4: hssfwb = new HSSFWorkbook(file) Any idea? – Extravert

@taimeili123 It looks like you're trying to open xlsx file, not xls. This should be done using XSSFWorkbook, not HSSFWorkbook. – Lippe

Thanks. I did what you said and the program ran without issue. However the problem is after running the program, I can no longer open the excel, it says the file type is not supported by the excel. Any suggestions would be greatly appreciated. – Extravert

@taimeili123 It's hard to say exactly what can be wrong. On my side it works perfectly with sample file. Probably some issues concerned to your specific excel file or NPOI build. – Lippe

Would you please let me know what other references you are using? I might be missing some references – Extravert

@taimeili123 Oh, I've found an issue you've told about. Missed it first time because saved file under another name after modification in test case... It looks very strange, but file is really gets corrupted after saving - probably some bug in NPOI. Quick "patch" which helped - is just delete file with File.Delete before opening it second time, and open it with FileMode.CreateNew. I've checked it and it seems to be working. – Lippe

Let us continue this discussion in chat. – Extravert

The references: using NPOI.HSSF.UserModel and using NPOI.SS.UserModel – Kneecap

@AndyKorneyev, it´s not necessary to delete the file. When creating the filestream for writing simply use FileMode.Create, this already resolves the issue of a corrupt file. – Nazarite

Answer 2

I find NPOI very usefull for working with Excel Files, here is my implementation with version 2 (Comments are in Spanish, sorry for that):

This Method Opens an Excel (both xls or xlsx) file and converts it into a DataTable.

        /// <summary>Abre un archivo de Excel (xls o xlsx) y lo convierte en un DataTable.
    /// LA PRIMERA FILA DEBE CONTENER LOS NOMBRES DE LOS CAMPOS.</summary>
    /// <param name="pRutaArchivo">Ruta completa del archivo a abrir.</param>
    /// <param name="pHojaIndex">Número (basado en cero) de la hoja que se desea abrir. 0 es la primera hoja.</param>
    private DataTable Excel_To_DataTable(string pRutaArchivo, int pHojaIndex)
    {
        // --------------------------------- //
        /* REFERENCIAS:
         * NPOI.dll
         * NPOI.OOXML.dll
         * NPOI.OpenXml4Net.dll */
        // --------------------------------- //
        /* USING:
         * using NPOI.SS.UserModel;
         * using NPOI.HSSF.UserModel;
         * using NPOI.XSSF.UserModel; */
        // AUTOR: Ing. Jhollman Chacon R. 2015
        // --------------------------------- //
        DataTable Tabla = null;
        try
        {
            if (System.IO.File.Exists(pRutaArchivo))
            {

                IWorkbook workbook = null;  //IWorkbook determina si es xls o xlsx              
                ISheet worksheet = null;
                string first_sheet_name = "";

                using (FileStream FS = new FileStream(pRutaArchivo, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(FS);          //Abre tanto XLS como XLSX
                    worksheet = workbook.GetSheetAt(pHojaIndex);    //Obtener Hoja por indice
                    first_sheet_name = worksheet.SheetName;         //Obtener el nombre de la Hoja

                    Tabla = new DataTable(first_sheet_name);
                    Tabla.Rows.Clear();
                    Tabla.Columns.Clear();

                    // Leer Fila por fila desde la primera
                    for (int rowIndex = 0; rowIndex <= worksheet.LastRowNum; rowIndex++)
                    {
                        DataRow NewReg = null;
                        IRow row = worksheet.GetRow(rowIndex);
                        IRow row2 = null;
                        IRow row3 = null;

                        if (rowIndex == 0)
                        {
                            row2 = worksheet.GetRow(rowIndex + 1); //Si es la Primera fila, obtengo tambien la segunda para saber el tipo de datos
                            row3 = worksheet.GetRow(rowIndex + 2); //Y la tercera tambien por las dudas
                        }

                        if (row != null) //null is when the row only contains empty cells 
                        {
                            if (rowIndex > 0) NewReg = Tabla.NewRow();

                            int colIndex = 0;
                            //Leer cada Columna de la fila
                            foreach (ICell cell in row.Cells)
                            {
                                object valorCell = null;
                                string cellType = "";
                                string[] cellType2 = new string[2];

                                if (rowIndex == 0) //Asumo que la primera fila contiene los titlos:
                                {
                                    for (int i = 0; i < 2; i++)
                                    {
                                        ICell cell2 = null;
                                        if (i == 0) { cell2 = row2.GetCell(cell.ColumnIndex); }
                                        else { cell2 = row3.GetCell(cell.ColumnIndex); }

                                        if (cell2 != null)
                                        {
                                            switch (cell2.CellType)
                                            {
                                                case CellType.Blank: break;
                                                case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
                                                case CellType.String: cellType2[i] = "System.String"; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType2[i] = "System.DateTime"; }
                                                    else
                                                    {
                                                        cellType2[i] = "System.Double";  //valorCell = cell2.NumericCellValue;
                                                    }
                                                    break;

                                                case CellType.Formula:                                                      
                                                    bool continuar = true;
                                                    switch (cell2.CachedFormulaResultType)
                                                    {
                                                        case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
                                                        case CellType.String: cellType2[i] = "System.String"; break;
                                                        case CellType.Numeric:
                                                            if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType2[i] = "System.DateTime"; }
                                                            else
                                                            {
                                                                try
                                                                {
                                                                    //DETERMINAR SI ES BOOLEANO
                                                                    if (cell2.CellFormula == "TRUE()") { cellType2[i] = "System.Boolean"; continuar = false; }
                                                                    if (continuar && cell2.CellFormula == "FALSE()") { cellType2[i] = "System.Boolean"; continuar = false; }
                                                                    if (continuar) { cellType2[i] = "System.Double"; continuar = false; }
                                                                }
                                                                catch { }
                                                            } break;
                                                    }
                                                    break;
                                                default:
                                                    cellType2[i] = "System.String"; break;
                                            }
                                        }
                                    }

                                    //Resolver las diferencias de Tipos
                                    if (cellType2[0] == cellType2[1]) { cellType = cellType2[0]; }
                                    else
                                    {
                                        if (cellType2[0] == null) cellType = cellType2[1];
                                        if (cellType2[1] == null) cellType = cellType2[0];
                                        if (cellType == "") cellType = "System.String";
                                    }

                                    //Obtener el nombre de la Columna
                                    string colName = "Column_{0}";
                                    try { colName = cell.StringCellValue; }
                                    catch { colName = string.Format(colName, colIndex); }

                                    //Verificar que NO se repita el Nombre de la Columna
                                    foreach (DataColumn col in Tabla.Columns)
                                    {
                                        if (col.ColumnName == colName) colName = string.Format("{0}_{1}", colName, colIndex);
                                    }

                                    //Agregar el campos de la tabla:
                                    DataColumn codigo = new DataColumn(colName, System.Type.GetType(cellType));
                                    Tabla.Columns.Add(codigo); colIndex++;
                                }
                                else
                                {
                                    //Las demas filas son registros:
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank: valorCell = DBNull.Value; break;
                                        case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
                                        case CellType.String: valorCell = cell.StringCellValue; break;
                                        case CellType.Numeric:
                                            if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                            else { valorCell = cell.NumericCellValue; } break;
                                        case CellType.Formula:
                                            switch (cell.CachedFormulaResultType)
                                            {
                                                case CellType.Blank: valorCell = DBNull.Value; break;
                                                case CellType.String: valorCell = cell.StringCellValue; break;
                                                case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                                    else { valorCell = cell.NumericCellValue; }
                                                    break;
                                            }
                                            break;
                                        default: valorCell = cell.StringCellValue; break;
                                    }
                                    //Agregar el nuevo Registro
                                    if (cell.ColumnIndex <= Tabla.Columns.Count - 1) NewReg[cell.ColumnIndex] = valorCell;
                                }
                            }
                        }
                        if (rowIndex > 0) Tabla.Rows.Add(NewReg);
                    }
                    Tabla.AcceptChanges();
                }
            }
            else
            {
                throw new Exception("ERROR 404: El archivo especificado NO existe.");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return Tabla;
    }

This Second method does the oposite, saves a DataTable into an Excel File, yeah it can either be xls or the new xlsx, your choise!

        /// <summary>Convierte un DataTable en un archivo de Excel (xls o Xlsx) y lo guarda en disco.</summary>
    /// <param name="pDatos">Datos de la Tabla a guardar. Usa el nombre de la tabla como nombre de la Hoja</param>
    /// <param name="pFilePath">Ruta del archivo donde se guarda.</param>
    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.ReadWrite))
                {
                    string Ext = System.IO.Path.GetExtension(pFilePath); //<-Extension del archivo
                    switch (Ext.ToLower())
                    {
                        case ".xls":
                            HSSFWorkbook workbookH = new HSSFWorkbook();
                            NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
                            dsi.Company = "Cutcsa"; dsi.Manager = "Departamento Informatico";
                            workbookH.DocumentSummaryInformation = dsi;
                            workbook = workbookH;
                            break;

                        case ".xlsx": workbook = new XSSFWorkbook(); break;
                    }

                    worksheet = workbook.CreateSheet(pDatos.TableName); //<-Usa el nombre de la tabla como nombre de la Hoja

                    //CREAR EN LA PRIMERA FILA LOS TITULOS DE LAS COLUMNAS
                    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);
                            iCol++;
                        }
                        iRow++;
                    }

                    //FORMATOS PARA CIERTOS TIPOS DE DATOS
                    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");

                    //AHORA CREAR UNA FILA POR CADA REGISTRO DE LA TABLA
                    foreach (DataRow row in pDatos.Rows)
                    {
                        IRow fila = worksheet.CreateRow(iRow);
                        int iCol = 0;
                        foreach (DataColumn column in pDatos.Columns)
                        {
                            ICell cell = null; //<-Representa la celda actual                               
                            object cellValue = row[iCol]; //<- El valor actual de la celda

                            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));
                                    }
                                    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));

                                        //Si No tiene valor de Hora, usar formato dd-MM-yyyy
                                        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;
        }
    }

With this 2 methods you can Open an Excel file, load it into a DataTable, do your modifications and save it back into an Excel file.

Hope you guys find this usefull.

Ideational

I can not comment but in the code from Jhollman no important issue that after loading the file workbook = WorkbookFactory.Create(FS); we should use XSSFFormulaEvaluator.EvaluateAllFormulaCells (workbook); to refresh formula calculations. Because they don't always load correctly (xls file generated from openoffice, libreoffice etc.) The same should be done immediately before saving to file. – Canalize