CODEWITHSUNDEEP
c#winforms
William
William

Reputation: 6610

Converting Excel File From .csv To .xlsx

I want my application to go and find a 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?

Upvotes: 25

Views: 85183

Answers (7)

zzhelev
zzhelev

Reputation: 11

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

Upvotes: 1

Christian Sauer
Christian Sauer

Reputation: 10949

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.

Upvotes: 4

Mark Redman
Mark Redman

Reputation: 24535

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

Upvotes: 14

costsoldier
costsoldier

Reputation: 11

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

Upvotes: 1

arash
arash

Reputation: 81

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

Upvotes: 7

Rakuen42
Rakuen42

Reputation: 1437

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

Upvotes: 18

cvraman
cvraman

Reputation: 1697

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.

Upvotes: 0

Related Questions