CODEWITHSUNDEEP
c#exceljet
Nader Sghir
Nader Sghir

Reputation: 81

Reading pivot table with c#

I am trying to read an excel-sheet containing a pivot table generated by macros with the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path;
Extended Properties="Excel 8.0;HDR=NO;";

the problem is that the values doesn't appear in my dataset, only labels of columns and lines, but not necessary values.

Any idea to read a such file?

Upvotes: 0

Views: 3609

Answers (4)

user1059804
user1059804

Reputation:

I made some amendment on the answer from user3644719. In his/her answer, there are some typo, like variable not defined in the code.

        String sHeet = "Pivot";

        DataTable excelTable = new DataTable();

        excelTable.Clear();
        string excelFile = "C:\\test_pivot.xlsx";
        Excel.Application excel = new Excel.Application();
        Excel.Workbook wb = excel.Workbooks.Open(excelFile);
        Excel.Worksheet pivotWorkSheet = (Excel.Worksheet)wb.Sheets[sHeet];
        Excel.Range xlRange = pivotWorkSheet.UsedRange;

        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

        string[] lColumns = new string[2] { "Column1","Column2" };

        if (excelTable.Columns.Count <= 0)
        {
            foreach (string column in lColumns)
            {
                excelTable.Columns.Add(column);
            }

        }

        Object[] exrow = new object[colCount];
        for (int row=1; row <= rowCount; row++)
        {
            for (int j = 1; j <= colCount; j++)
            {  
                exrow[j - 1] = xlRange.Cells[row, j].Text;
                Console.WriteLine(xlRange.Cells[row, j].Text);
            }

            excelTable.Rows.Add(exrow);
            //excelTable.Rows.Add(exrow);
        }

        String str = excelTable.ToString();
    }

Upvotes: 0

user3644719
user3644719

Reputation: 21

This will read and excel table into data table in C#. 'sHeet' is the name of the sheet in Excel and startfromrow is where you want the table read to start from. excelTable is the Data Table where the data will be stored.

public void ReadEx(string sHeet, int startfromrow)
       {
          excelTable.Clear();
          string excelFile =  "filepath";
          Excel.Application excel = new Excel.Application();
          Excel.Workbook wb = excel.Workbooks.Open(excelFile);
          Excel.Worksheet pivotWorkSheet = (Excel.Worksheet)wb.Sheets[sHeet];
          Excel.Range xlRange = pivotWorkSheet.UsedRange;

          int rowCount = xlRange.Rows.Count;
          int colCount = xlRange.Columns.Count;


          if (excelTable.Columns.Count <= 0)
            {
              excelTable.Columns.Add("Row Labels");
              foreach (string column in lColumns)
              {
                  excelTable.Columns.Add(column);
              }
            }

          object[] exrow = new object[colCount];
          for (int i; startfromrow <= rowCount; row++)
          {
              for (int j = 1; j <= colCount; j++)
              {
                  exrow[j-1] = xlRange.Cells[startfromrow , j].Text;
                  Console.WriteLine(xlRange.Cells[startfromrow , j].Text);
              }
              excelTable.Rows.Add(exrow);
          }

Upvotes: 0

Nader Sghir
Nader Sghir

Reputation: 81

I don't know why, but by changing the provider to Provider=Microsoft.ACE.OLEDB.12.0, data is read correctly.

the fill dataset is slower, i but I got what i'm looking for thanks

Upvotes: 1

Ewerton
Ewerton

Reputation: 4533

I dont know the answer, but maybe Epplus (http://epplus.codeplex.com/) could help you.

Upvotes: 1

Related Questions