C’è un modo semplice per convertire il file .xls in file .csv? (Eccellere)

C’è un modo semplice per convertire il file .xls in file .csv? (Eccellere)

nel codice C #?

Intendo prendere un file .xls esistente e convertirli in un file .csv

Grazie in anticipo

.SaveAs() il checkout del metodo .SaveAs() nell’object Excel.

 wbWorkbook.SaveAs("c:\yourdesiredFilename.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV) 

O seguendo:

 public static void SaveAs() { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1]; Microsoft.Office.Interop.Excel.Range thisCell = (Microsoft.Office.Interop.Excel.Range)CurSheet.Cells[1, 1]; thisCell.Value2 = "This is a test."; wbWorkbook.SaveAs(@"c:\one.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wbWorkbook.SaveAs(@"c:\two.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wbWorkbook.Close(false, "", true); } 

Ecco un metodo C # per farlo. Ricordati di aggiungere la tua gestione degli errori – questo per lo più presuppone che le cose funzionino per brevità. È solo il framework 4.0+, ma questo è principalmente dovuto al parametro worksheetNumber opzionale. È ansible sovraccaricare il metodo se è necessario supportare versioni precedenti.

 static void ConvertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1) { if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath); if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile); // connection string var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath); var cnn = new OleDbConnection(cnnStr); // get schema, then data var dt = new DataTable(); try { cnn.Open(); var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet"); string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", ""); string sql = String.Format("select * from [{0}]", worksheet); var da = new OleDbDataAdapter(sql, cnn); da.Fill(dt); } catch (Exception e) { // ??? throw e; } finally { // free resources cnn.Close(); } // write out CSV data using (var wtr = new StreamWriter(csvOutputFile)) { foreach (DataRow row in dt.Rows) { bool firstLine = true; foreach (DataColumn col in dt.Columns) { if (!firstLine) { wtr.Write(","); } else { firstLine = false; } var data = row[col.ColumnName].ToString().Replace("\"", "\"\""); wtr.Write(String.Format("\"{0}\"", data)); } wtr.WriteLine(); } } } 

Installa questi 2 pacchetti

     

Funzione di supporto

 using ExcelDataReader; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ExcelToCsv { public class ExcelFileHelper { public static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath) { using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { IExcelDataReader reader = null; if (excelFilePath.EndsWith(".xls")) { reader = ExcelReaderFactory.CreateBinaryReader(stream); } else if (excelFilePath.EndsWith(".xlsx")) { reader = ExcelReaderFactory.CreateOpenXmlReader(stream); } if (reader == null) return false; var ds = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { UseHeaderRow = false } }); var csvContent = string.Empty; int row_no = 0; while (row_no < ds.Tables[0].Rows.Count) { var arr = new List(); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { arr.Add(ds.Tables[0].Rows[row_no][i].ToString()); } row_no++; csvContent += string.Join(",", arr) + "\n"; } StreamWriter csv = new StreamWriter(destinationCsvFilePath, false); csv.Write(csvContent); csv.Close(); return true; } } } } 

Uso:

 var excelFilePath = Console.ReadLine(); string output = Path.ChangeExtension(excelFilePath, ".csv"); ExcelFileHelper.SaveAsCsv(excelFilePath, output); 

Devo fare la stessa cosa Ho finito con qualcosa di simile a Kman

  static void ExcelToCSVCoversion(string sourceFile, string targetFile) { Application rawData = new Application(); try { Workbook workbook = rawData.Workbooks.Open(sourceFile); Worksheet ws = (Worksheet) workbook.Sheets[1]; ws.SaveAs(targetFile, XlFileFormat.xlCSV); Marshal.ReleaseComObject(ws); } finally { rawData.DisplayAlerts = false; rawData.Quit(); Marshal.ReleaseComObject(rawData); } Console.WriteLine(); Console.WriteLine($"The excel file {sourceFile} has been converted into {targetFile} (CSV format)."); Console.WriteLine(); } 

Se ci sono più fogli, questo si perde nella conversione, ma è ansible ripetere il ciclo sul numero di fogli e salvarli singolarmente come csv.