I need to read each cell with data line by line from the excel file and fill in the collection with these lines. That is, one excel line is one collection item, the second line is the second collection item. The problem is that commas are not placed between the cells. I can not understand what the problem is. What am I doing wrong? To separate commas, I use the AppendFormat method of the StringBuilder class. Here is my code that performs this task:

public class ExcelReader { private Excel.Application xlApp; private Excel.Workbook xlWorkbook; private Excel.Worksheet xlSheet; private Excel.Range xlRange; public List<string> ExcelData { get; private set; } public void ImportFromExcel(string filePath = @"C:\Книга2.xlsx") { ExcelData = new List<string>(); xlApp = new Excel.Application(); xlWorkbook = xlApp.Workbooks.Open(filePath); xlSheet = xlWorkbook.Sheets[1]; xlRange = xlSheet.UsedRange; var numberOfRows = xlRange.Rows.Count; var numberOfCols = xlRange.Columns.Count; var strBuilder = new StringBuilder(); for (var row = 1; row <= numberOfRows; row++) { for (var column = 1; column <= numberOfCols; column++) { if (xlRange.Cells[row, column].Value2 != null) { strBuilder.AppendFormat("{0}", xlRange.Cells[row, column].Value2.ToString()); } } ExcelData.Add(strBuilder.ToString()); strBuilder.Clear(); } } }
  • one
    And where actually commas ?? In Appendix Format, you add only the value of the cell ... - wind
  • And in general, you do not need the AppendFormat, formatting something trivial. Use just Append. - VladD

1 answer 1

I use the following script. He, as for me, is quite comfortable and minimalistic. And also it resolves a part of the problems that you will encounter in any case if you use the Interop way. Such as unkillable Excel process or erasing empty cells, normal parsing of cells with a formula (the formula returns, not the value after calculating the formula)

But bear in mind that the script, although working, is rather slow in work. But for everyday use with small documents will fit more than. Especially because it is very easy to use.

Example of use:

 Excel excel = new Excel(); excel.FileOpen("C:\file1.xlsx"); var a = excel.Rows[1][14] //возвратит тебе 14тую ячейку в 1 строке документа excel.Rows.Add("asdf","=5*4", "<- сдесь будет написано 20"); excel.FileSave("C:\file2.xlsx");

and this is the class code itself

 namespace BotAgent.Ifrit.DataExporter { using System; using System.Collections.Generic; using System.Linq; using Microsoft.Office.Interop.Excel; using System.Threading; using System.Diagnostics; using System.IO; using System.Runtime.InteropServices; public class Excel { [DllImport("user32.dll")] private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId); private Application _excelApp; private Workbook _excelWorkBook; private Worksheet _excelSheet; public List<List<string>> Rows = new List<List<string>>(); private void InitExcelApp() { _excelApp = new Application(); _excelWorkBook = _excelApp.Workbooks.Add(); _excelSheet = (Worksheet)_excelWorkBook.Sheets[1]; } /// <summary> /// I know that this is wrong way to do like this, /// but this is really most easy way to fix non-killing Excel process issue /// </summary> private void CloseExcelApp() { int hWnd = _excelApp.Application.Hwnd; uint processID; GetWindowThreadProcessId((IntPtr)hWnd, out processID); Process.GetProcessById((int)processID).Kill(); _excelWorkBook = null; _excelApp = null; _excelSheet = null; } public void FileOpen(string path) { Stopwatch st = new Stopwatch(); InitExcelApp(); Rows.Clear(); _excelWorkBook = _excelApp.Workbooks.Open(path); _excelSheet = (Worksheet)_excelWorkBook.Sheets[1]; //Fix for empty cells that excel see as not empty _excelSheet.Columns.ClearFormats(); _excelSheet.Rows.ClearFormats(); int lastRow = _excelSheet.UsedRange.Rows.Count; int lastCell = _excelSheet.UsedRange.Columns.Count; st.Start(); //SLOW!!!!!! for (int i = 1; i <= lastRow; i++) { Rows.Add(new List<string>()); for (int j = 1; j <= lastCell; j++) { var tmp = (_excelSheet.Cells[i, j]).Formula; var value = (tmp != null) ? tmp.ToString() : string.Empty; Rows[Rows.Count-1].Add(value); } } //SLOW!!!!!! Console.WriteLine("3rd block take " + st.Elapsed); st.Restart(); RemoveGarbageFromRows(lastRow, lastCell); CloseExcelApp(); } private void RemoveGarbageFromRows(int lastRow, int lastCell) { for (int i = 0; i < lastRow; i++) { for (int j = lastCell - 1; j > 0; j--) { if (Rows[i][j] == "") Rows[i].RemoveAt(j); else { j = 0; } } } } public void FileSave(string path, bool hideExcelPopupsAndAlerts = true) { InitExcelApp(); CreateDirIfNotExist(path, true); MoveRowsToExcelRows(); _excelApp.DisplayAlerts = !hideExcelPopupsAndAlerts; for (int i = 0; i <= 3; i++) { try { _excelWorkBook.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); i = 4; } catch (Exception e) { Thread.Sleep(500); } } CloseExcelApp(); } public void AddRow(params string[] cells) { Rows.Add(cells.ToList()); } private void MoveRowsToExcelRows () { for (int i = 0; i < Rows.Count; i++) { for (int j = 0; j < Rows[i].Count; j++) { _excelSheet.Cells[i+1, j+1] = Rows[i][j]; } } } private void CreateDirIfNotExist(string dirPath, bool removeFilename=false) { if (removeFilename) { dirPath = Directory.GetParent(dirPath).FullName; } if (!Directory.Exists(dirPath)) { Directory.CreateDirectory(dirPath); } } } }

PS: there is a similar rather fast class for working with csv. The structure is the same as here. CSV is much easier and faster. + tsv can be imported into eksel. Hardly that, I can throw too.

PS2: well, it’s easy to make join with my script line by line and you’ll have what you asked :)