0

I created a datatable from an excel sheet. I ran some linq queries and populated the needed columns in a new worksheet. I need help filling in the rows based on the date column. It needs to be dynamic because dates change and IDs could change.

I have an excel sheet that has data like this

 L ID   | Date    |  Other | Columns |
  D123  1/24/2018
  D456  1/25/2018
  D678  1/26/2018
  D910  1/25/2018

I am trying to create output that looks like this. 

    L ID   1/24/2018  1/25/2018 1/26/2018
    D123     1
    D456                1
    D678                          1
    D910                1

I did some code that selected the data needed and then populated the date columns and the ID column in an excel worksheet. 

public List<DateTime?> GetTurnOverDates(System.Data.DataTable dt3)
        {

            List<DateTime?> TurnOverDateList = dt3.AsEnumerable().Select(r => r.Field<DateTime?>("Turnover")).Distinct().OrderBy(x => x).Where(x => x >= DateTime.Today.AddDays(-1)).ToList();

          return TurnOverDateList;

public List<String> GetLIDList(System.Data.DataTable dt3, List<DateTime?> list)
        {
            List<String> LIDList = dt3.AsEnumerable().Where(r => list.Contains(r.Field<DateTime?>("Turnover"))).Select(r => r.Field<String>("LID")).ToList();
            return LidList;
        }

public void CreateTurnoverWS(String Path, List<DateTime?> DateList, List<String> LidList)
        {
            int rw = 0;
            int c1 = 0;

            Excel.Application xlsApp;
            Excel.Workbook xlsWorkbook;
            Excel.Range range;

            xlsApp = new Excel.Application();
            xlsWorkbook = xlsApp.Workbooks.Open(Path);
            Excel.Sheets xlsworksheets = xlsWorkbook.Worksheets;
            var xlsNewSheet = (Excel.Worksheet)xlsworksheets.Add(xlsworksheets[1], Type.Missing, Type.Missing, Type.Missing);
            xlsNewSheet.Name = "Turnover";
            xlsNewSheet.Cells[1, 3] = "L ID";



            xlsNewSheet = (Excel.Worksheet)xlsWorkbook.Worksheets.get_Item(1);
            xlsNewSheet.Select();

            range = xlsNewSheet.UsedRange;
            rw = range.Rows.Count;
            c1 = range.Columns.Count;

            for (int cCnt = 1; cCnt < DateList.Count(); cCnt++)
            {
                xlsNewSheet.Cells[1, c1++] = DateList[cCnt];
            }

            for (int ccnt2 = 0; ccnt2 < LidList.Count(); ccnt2++)
            {
                xlsNewSheet.Cells[rw++, 3] = LidList[ccnt2];
            }



            xlsWorkbook.Save();
            xlsWorkbook.Close();


        }

    }

So I basically have this so far in the excel sheet. 

        L ID        1/24/2018  1/25/2018 1/26/2018
        D123    
        D456                
        D678 
        D910

I was researching pivot tables but I am not sure if it will work. I have data going from excel to a datatable, selecting certain data then putting it into a new worksheet. Please advise. Thanks.

user10471443
  • 27
  • 8

1 Answers1

0

For „pivot“-ing have a look at Is it possible to Pivot data using LINQ?

PS: For accessing excel files from code I usually use epplus

Enrico
  • 21
  • 3