to split table cells comma, reading them line by line from Excel file

The question was asked: 49 years 10 months ago   views: 12

I need to read each cell data line by line from the excel file and fill in these lines collection. That is, one line excel is one element of a collection, the second line is the second element of the collection. The problem is that no commas are placed between cells. Can't understand what the problem is. What am I doing wrong? For the commas I use 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(); } } }
Asked: 01-01-1970 в 03:00:00
Where's the comma?? In Appendformat you only add the cell value... - 18-01-2013 в 22:48:28
And anyway, you don't need AppendFormat formatting something trivial. Just use Append. - 19-01-2013 в 01:11:13

Answers   1


I use the following script. He, to me, quite comfortable and minimalistic. And it solved some of the problems which you will face if you use Interop. Like unkillable process eksele or podkamenie empty cells, the normal parsing of the cells with a formula (specifically, the formula is returned and not the value after the calculation of the formula)

But be warned, the script is working though, it is quite slow in operation. But pusakanegara for use with small documents will fit more than. Especially,it is very easy to use.

Example usage:

Excel excel = new Excel(); excel.FileOpen("C:\file1.xlsx"); var a = excel.Rows[1][14] //will return you 14тую cell in 1 row of the document excel.Rows.Add("asdf","=5*4", "<- 20 here will be written"); excel.FileSave("C:\file2.xlsx");

and this is the code of the class

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 the 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.Download, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Download, Type.Download, Type.Download, Type.Download, 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 still such a fairly fast class to work with csv. The structure is the same as here. Tssv is much easier and faster. + tssv you can import to Excel. Just that, I can also throw.

PS2: well, my script line by line to make the join will not be difficult and you will have what you asked :)

Answered: 17-08-2016 в 02:20:02