C# Create/Modify/Read .Xlsx Files

C# create/modify/read .xlsx files

With EPPlus it's not required to create file, you can do all with streams, here is an example of ASP.NET ashx handler that will export datatable into excel file and serve it back to the client :

  public class GetExcel : IHttpHandler
  {
    public void ProcessRequest(HttpContext context)
    {
      var dt = DBServer.GetDataTable("select * from table");
      var ms = GetExcel.DataTableToExcelXlsx(dt, "Sheet1");
      ms.WriteTo(context.Response.OutputStream);
      context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
      context.Response.AddHeader("Content-Disposition", "attachment;filename=EasyEditCmsGridData.xlsx");
      context.Response.StatusCode = 200;
      context.Response.End();   
    }

    public bool IsReusable
    {
      get
      {
        return false;
      }
    }

    public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
    {
      var result = new MemoryStream();
      var pack = new ExcelPackage();
      var ws = pack.Workbook.Worksheets.Add(sheetName);

      int col = 1;
      int row = 1;
      foreach (DataRow rw in table.Rows)
      {
        foreach (DataColumn cl in table.Columns)
        {
          if (rw[cl.ColumnName] != DBNull.Value)
            ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
          col++;
        }
        row++;
        col = 1;
      }
      pack.SaveAs(result);
      return result;
    }
  }

Update XLSX file changes whilst reading the file with XmlReader

I did some more modifications with @dbc's help and now it works as I wanted.

PackagePartCollection ppc = this.Package.GetParts();
foreach (PackagePart pp in ppc)
{
  try
  {
     if (!this.xmlContentTypesXlsx.Contains(pp.ContentType)) continue;

     string tempFilePath = GetTempFilePath();
     
     using (XmlReader reader = XmlReader.Create(pp.GetStream()))
     {
        using (XmlWriter writer = this.Package.FileOpenAccess == FileAccess.ReadWrite ? XmlWriter.Create(tempFilePath) : null)
        {
          while (reader.EOF == false)
          {
             if (reader.NodeType == XmlNodeType.Element && reader.Name == "hyperlinks")
             {
                   XmlDocument doc = new XmlDocument();
                   XmlNode rootNode = doc.ReadNode(reader);
                   if (rootNode != null)
                   {
                        ProcessNode(rootNode);
                        if (writer != null)
                        {
                            rootNode.WriteTo(writer);
                        }
                   }
              }
              else if (reader.NodeType == XmlNodeType.Element && reader.Name == "row")
              {
                    XmlDocument doc = new XmlDocument();
                    XmlNode rootNode = doc.ReadNode(reader);

                    if (rootNode != null)
                    {
                        ProcessNode(rootNode);
                        if (writer != null)
                        {
                            rootNode.WriteTo(writer);
                        }
                    }
              }
              else
              {
                    WriteShallowNode(writer, reader); // Used from the @dbc's suggested StackOverflow answers
                    reader.Read();
              }
            }
         }
      }

      if (this.packageChanged) // is being set in ProcessNode method
      {
          this.packageChanged = false;

          using (var tempFile = File.OpenRead(tempFilePath))
          {
               tempFile.CopyTo(pp.GetStream(FileMode.Create, FileAccess.Write));
          }
       }
   }
   catch (OutOfMemoryException)
   {
        throw;
   }
   catch (Exception ex)
   {
      Log.Exception(ex, @"Failed to process a file."); // our inner log method
   }
   finally
   {
       if (!string.IsNullOrWhiteSpace(tempFilePath))
       {
            // Delete temp file
       }
   }
}

C# modifiing Excel files, keeping the style

I would recommend using the OpenXML SDK from Microsoft. It allows you to do about anything with the newer xlsx files (Excel 2007 and up) using C#.

OpenXML SDK for Office

Edit existing Excel file C# npoi

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

Read and Write Excel Files C#

If you are willing to commit yourself to a later version of Excel (2007+) you can also take a look at the OpenXML SDK. It's free, doesn't tie you to having MS Office installed on the machine it will be running on and there are quite a few resources available on how to use it online (including blogs from the OpenXML team).

---

Related Topics