How to read a excel file in C # and save the data into strings? [duplicate]

2

    
asked by anonymous 21.10.2015 / 12:31

3 answers

2

I use GemBox.Spreadsheet 3.7 for .NET 3.0 - 4.5 to do Excel import

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 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;
    }
    
21.10.2015 / 14:53
1

Augusto Bueno, as he did not mention 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-excel-%E2%80%9Cxls%E2%80%9D-com-csharp.aspx this can help you to drive in one direction.

    
21.10.2015 / 13:20
1

I already use ExcelLib ( link ); I researched now and saw that it appears as an archived project ...

The advantage is to manipulate (creating, opening, saving, etc.) without the need to have MSOffice installed (with the limitation of being only ".xls").

Maybe it's even more than you need.

Good work

    
21.10.2015 / 15:01