How to read an excel file in c# and save the data in strings?

Question

Guys, I need to make a c# console application that reads an Excel and saves each data in a string!! I’ve tried several libraries and nothing has worked out

Answer 1

I use Gembox.Spreadsheet 3.7 for . NET 3.0 - 4.5 to import from Excel

protected void lnkValidar_Click(object sender, EventArgs e)
{
    if (FileUpload.HasFile)
        {
            FileInfo finfo = new FileInfo(FileUpload.FileName);
            string fileExtension = finfo.Extension.ToLower();
            if (fileExtension != ".xlsx")
            {
                lblErro.UserError("Selecione um arquivo no formato .xlsx");
                return;
            }

            Stream fileStream = FileUpload.FileContent;
            var RetornoDados = ConverteExcelExcelFile(fileStream);

                var Msg = new StringBuilder();
                foreach (var sheet in RetornoDados.Worksheets)
                {
                    foreach (var row in sheet.Rows.Where(R => R.Index > 0))
                    {
                        try
                        {
                            Msg.Clear();
                            String Coluna0 = (Convert.ToString(row.AllocatedCells[0].Value)).Trim();
                            String Coluna1 = (Convert.ToString(row.AllocatedCells[1].Value)).Trim();
                            String Coluna2 = (Convert.ToString(row.AllocatedCells[2].Value)).Trim();
                            String Coluna3 = (Convert.ToString(row.AllocatedCells[3].Value)).Trim();

                         }
                        catch (Exception exc)
                        {
                            throw new Exception("Erro ");
                        }
                    }
                    break;     // Só o 1o sheet !
                }
        }
    }
}

public ExcelFile ConverteExcelExcelFile(Stream fileStream)
{
    SpreadsheetInfo.SetLicense("000000000000000"); // entre no site e pegue uma licença 
    return ExcelFile.Load(fileStream, LoadOptions.XlsxDefault);            
}

Or the Open XML ...

            if (FileUpload.HasFile)
            {
                FileInfo finfo = new FileInfo(FileUpload.FileName);
                string fileExtension = finfo.Extension.ToLower();
                if (fileExtension != ".xlsx")
                {
                    lblErro.UserError("Selecione um arquivo no formato .xlsx");
                    return;
                }

                Stream fileStream = FileUpload.FileContent;
                var retorno = Upload(fileStream);

                if (retorno is string)
                {
                  //erro
                }
                else
                {
                    // todo
                }
            }


    private Object Upload(Stream fileStream)
    {
        var dt = new DataTable();

        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileStream, false))
        {
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            Worksheet workSheet = worksheetPart.Worksheet;
            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
            IEnumerable<Row> rows = sheetData.Descendants<Row>();
            try
            {
                foreach (Cell cell in rows.ElementAt(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }
            }
            catch
            {                   
                    throw;
            }             
            try
            {
                int index = 0;

                foreach (Row row in rows)
                {
                    DataRow tempRow = dt.NewRow();

                    // Menor que a quantidade de colunas da planilha ... index
                    for (int i = 0; i < rows.Count(); i++) // index
                    {
                        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    dt.Rows.Add(tempRow);
                }
            }
            catch
            {
                dt.Rows.RemoveAt(0);
                return dt;
            }
        }

        dt.Rows.RemoveAt(0);
        return dt;
    }


    public static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
        string value = "";
        if (cell.CellValue != null)
            value = cell.CellValue.InnerText;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
        return value;
    }

Answer 2

Augusto Bueno, as he did not quote other details just reading the excel file, if the file will contain fixed fields and etc. This example, http://www.linhadecodigo.com.br/artigo/1895/lendo-um-arquivo-do-excel-%E2%80%9Cxls%E2%80%9D-com-csharp.aspx this may assist in driving in a direction.

Answer 3

I already use Excellib(https://code.google.com/p/excellibrary/); I just looked it up and saw it listed as archived project...

The advantage is to manipulate (creating, opening, saving, etc.) without having Msoffice installed (with the limitation being only for ". xls").

Maybe it’s even more than you need.

Good job