Converting Excel File From .csv To .xlsx

Question

I want my application to go and find a csv excel file and convert it into a .xlsx file instead.

Here's what I'm currently doing;

var fileName = @"Z:\0328\orders\PurchaseOrder.csv";
FileInfo f = new FileInfo(fileName);
f.MoveTo(Path.ChangeExtension(fileName, ".xlsx"));
var Newfile = @"Z:\0328\orders\PurchaseOrder.xlsx";

Now this does work. It changes the file extension to my desired format. However, the file then become 'corrupt' or at least Excel refuses to open it and neither will my application when I try to venture further.

Does anyone have a solution/work-around?

Answer 1

For those who want to use Interop instead of an external library, you can simply do this:

Application app = new Application();
Workbook wb = app.Workbooks.Open(@"C:\testcsv.csv", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(@"C:\testcsv.xlsx", XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();

The second argument of Workbook.SaveAs determines the true format of the file. You should make the filename extension match that format so Excel doesn't complain about corruption. You can see a list of the types and what they mean on MSDN.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

As always, please keep Microsoft's considerations in mind if this functionality is intended for a server environment. Interop may not be the way to go in that situation:

http://support.microsoft.com/kb/257757

Answer 2

I would parse in the CSV file and use this to write out an Excel file : https://github.com/JanKallman/EPPlus

Answer 3

This code should open the file you want and save it to the format without corrupting it.

1. Renames the file
2. Creates the Excel.Application instance
3. Opens the file
4. Does a save as to the desired format

5. Closes it

   using Excel = Microsoft.Office.Interop.Excel;
   
   private void Convert_CSV_To_Excel()
   {
   
       // Rename .csv To .xls
       System.IO.File.Move(@"d:\Test.csv", @"d:\Test.csv.xls");
   
       var _app = new Excel.Application();
       var _workbooks = _app.Workbooks;
   
       _workbooks.OpenText("Test.csv.xls",
                                DataType: Excel.XlTextParsingType.xlDelimited,
                                TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
                                ConsecutiveDelimiter: true,
                                Semicolon: true);
   
       // Convert To Excle 97 / 2003
       _workbooks[1].SaveAs("NewTest.xls", Excel.XlFileFormat.xlExcel5);
   
       _workbooks.Close();
   }
   

Answer 4

I would recommend using the following technique:

1. http://kbcsv.codeplex.com/ this reads CSV files in very easily and is very robust.
2. Create a datatable from the csv via the kbcsv extensions.
3. Use the eppplus library and its LoadFromDataTable to create a valid xlsx file (https://github.com/JanKallman/EPPlus)
4. done!

Advantages:

• It is faster than excel interop
• KBCSV is more robust than excels csv reading methods.
• It is availabe in environments witohout office.

Answer 5

Try this class; takes in any CSV or TXT file with any delimiter including a tab and converts to Excel (.xls)

examples:

• convertToExcel(@"path to file", "\t", ".csv");

• convertToExcel(@"path to file", "\|", ".txt");

  public static void convertToExcel(string fileName, string splitter, string extension)
  {
      string newFileName = fileName.Replace("." + extension, ".xls");
  
      string[] lines = File.ReadAllLines(fileName, Encoding.UTF8);
  
      int columnCounter = 0;
  
      foreach (string s in lines)
      {
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          if (ss.Length > columnCounter)
              columnCounter = ss.Length;
      }           
  
      HSSFWorkbook workbook = new HSSFWorkbook();
      var sheet = workbook.CreateSheet("Data");
      var rowIndex = 0;
      var rowExcel = sheet.CreateRow(rowIndex);
  
      foreach (string s in lines)
      {
          rowExcel = sheet.CreateRow(rowIndex);
  
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          for (int i = 0; i < columnCounter; i++)
          {
              string data = !String.IsNullOrEmpty("s") && i < ss.Length ? ss[i] : "";
              rowExcel.CreateCell(i).SetCellType(CellType.String);
              rowExcel.CreateCell(i).SetCellValue(data);                    
          }
  
          rowIndex++;
      }
  
      for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
          sheet.AutoSizeColumn(i);
  
      using (FileStream file = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
      {
          workbook.Write(file);
          file.Close();
      }
  }
  

Answer 6

I would recommend Closed XML which is a wrapper around Open XML SDK. Check out their examples. It's pretty easy to create a .xlsx file.

Answer 7

public static void ConvertFromCsvWithNpoi(string fileName, char splitter)
{
    var newFileName = Path.ChangeExtension(fileName, ".xlsx");
    string[] lines = File.ReadAllLines(fileName, Encoding.UTF8);

    IWorkbook workbook = new XSSFWorkbook();
    var sheet = workbook.CreateSheet(Path.GetFileName(newFileName));
    var rowIndex = 0;

    foreach (string line in lines)
    {
        var row = sheet.CreateRow(rowIndex);

        string[] lineStr = line.Trim().Split(splitter);

        for (int i = 0; i < lineStr.Length; i++)
        {
            string data = String.IsNullOrEmpty(line) ? string.Empty : lineStr[i].Trim();
            double sum = 0;
            if (double.TryParse(data, out sum))
            {
                row.CreateCell(i).SetCellValue(sum);
            }
            else
            {
                row.CreateCell(i).SetCellValue(data);
            }
        }

        rowIndex++;
    }

    for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
        sheet.AutoSizeColumn(i);

    using (FileStream file = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
    {
        workbook.Write(file);
        file.Close();
    }
}