none
Reading Excel Rows and transpose this RRS feed

  • Question

  • Hi,

    I am download a spreadsheet from Google Docs and then I am saving this as a ".XLSX" document. Once this is saved I want to loop via each row inside the sheet and transpose this data and then save it as a ".txt" or ".pdf" file. I am stuck on how to successfully do this though. What is the best way to accomplish this? Is it via the Interop or to do it via a OLEDB connection? Below is what I have tried and played around with. 

    public static Boolean downloadAsXLSX(DriveService _service, File _fileResource, string _saveTo)
    
            {
    
                if (!String.IsNullOrEmpty(_fileResource.ExportLinks["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]))
    
                {
    
                    try
    
                    {
    
                        var x = _service.HttpClient.GetByteArrayAsync(_fileResource.ExportLinks["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]);
    
                        byte[] arrBytes = x.Result;
    
                        System.IO.File.WriteAllBytes(_saveTo + ".xlsx", arrBytes);
    
                        return true;
    
                    }
    
                    catch (Exception e)
    
                    {
    
                        MessageBox.Show("An error occurred: " + e.Message);
    
                        return false;
    
                    }
    
                }
    
                else
    
                {
    
                    return false;
    
                }    
    
            }
    
            
            private void Download_Load(object sender, EventArgs e)
    
            {
    
                string[] scopes = new string[] { DriveService.Scope.Drive, DriveService.Scope.DriveFile };
    
     
    
                var keyFilePath = @"C:\\collegekey.p12";
    
                //var keyFilePath = @"D:\\test\\Download Google Docs\\GoogleKey.p12";
    
                var serviceAccountEmail = "test@developer.gserviceaccount.com";
    
     
    
                var certificate = new X509Certificate2(keyFilePath, "notasecret", X509KeyStorageFlags.Exportable);
    
                var credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer(serviceAccountEmail)
    
                {
    
                    Scopes = scopes
    
                }.FromCertificate(certificate));
    
     
    
                var service = new DriveService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = "Spreadsheets" });
    
     
    
                FilesResource.ListRequest request = service.Files.List();
    
                request.MaxResults = 1000;
    
                FileList files = request.Execute();
    
                foreach (File list in files.Items)
    
                {
    
                    if (list.Title == "Part-time Application Form 2015-16 (Responses)")
    
                    {
    
                        string title = "\\\\path\\path\\Client Services\\Applications\\Part-Time Responses 1516\\" + list.Title.ToString() + DateTime.Now.ToString("hh.mm") + " " + DateTime.Now.ToString("dd.MM.yy");
    
                        string pathTitle = title + ".xlsx";
    
                        downloadAsXLSX(service, list, title);
    
                        Excel.Application excelApp = new Excel.Application();
    
                        excelApp.Visible = false;
    
                        string workbookPath = title + ".xlsx";
    
                        Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    
                        Excel.Sheets excelSheets = excelWorkBook.Worksheets;
    
                        string currentSheet = "Form Responses 1";
    
                        Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
    
     
    
     
    
                        string sSheetName = null;
    
                        string sConnection = null;
    
                        DataTable dtTablesList = default(DataTable);
    
                        OleDbCommand oleExcelCommand = default(OleDbCommand);
    
                        OleDbConnection oleExcelConnection = default(OleDbConnection);
    
     
    
                        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathTitle + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
    
     
    
                        oleExcelConnection = new OleDbConnection(sConnection);
    
                        oleExcelConnection.Open();
    
     
    
                        dtTablesList = oleExcelConnection.GetSchema("Tables");
    
     
    
                        if (dtTablesList.Rows.Count > 0)
    
                        {
    
                            sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
    
                        }
    
     
    
                        dtTablesList.Clear();
    
                        dtTablesList.Dispose();
    
     
    
     
    
                        if (!string.IsNullOrEmpty(sSheetName))
    
                        {
    
                            oleExcelCommand = oleExcelConnection.CreateCommand();
    
                            oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
    
                            oleExcelCommand.CommandType = CommandType.Text;
    
                            OleDbDataAdapter g = new OleDbDataAdapter(oleExcelCommand);
    
                            DataTable dt = new DataTable();
    
                            g.Fill(dt);
    
                            if (dt.Rows.Count > 0)
    
                            {
    
                                foreach (DataRow dr in dt.Rows)
    
                                {
    
                                    
    
                                }
    
                            }
    
                        }
    
                        oleExcelConnection.Close();
    
                        //Excel.Application excelApp = new Excel.Application();
    
                        //excelApp.Visible = false;
    
                        //string workbookPath = title + ".xlsx";
    
                        //Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(workbookPath,0,false,5,"","",false,Excel.XlPlatform.xlWindows,"",true,false,0,true,false,false);
    
                        //Excel.Sheets excelSheets = excelWorkBook.Worksheets;
    
                        //string currentSheet = "Form Responses 1";
    
                        //Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
    
                        ////int rows = excelWorkSheet.UsedRange.Rows.Count - 1;
    
                        ////int columns = excelWorkSheet.UsedRange.Columns.Count;
    
                       
    
     
    
                        //Excel.Range first = excelWorkSheet.get_Range("A1", Type.Missing);
    
                        //Excel.Range last = excelWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
    
     
    
                        //Excel.Range ws = excelWorkSheet.get_Range(first, last);
    
     
    
                        //object arr = ws.Value;
    
     
    
                        //foreach (object s in (Array)arr)
    
                        //{
    
                        //    MessageBox.Show(s.ToString());
    
                        //}
    
     
    
     
    
                        //Excel.Range range = excelWorkSheet.get_Range("A1", last);
    
                        //Excel.Range ourRange = excelWorkSheet.UsedRange;
    
     
    
     
    
                        //Excel.Range excellCell = (Excel.Range)excelWorkSheet.get_Range("A1", "A1");
    
                        //MessageBox.Show(excellCell.Value);
    
     
    
                        //excelWorkSheet.Application.Quit();
    
                    }
    
                }
    
                System.Windows.Forms.Application.Exit();
    
            }
    
        }
    
    }
    Any help would be great! :)


    • Edited by JK95 Wednesday, October 28, 2015 8:08 PM
    • Moved by Kristin Xie Thursday, October 29, 2015 8:58 AM move to appropriate forum
    Wednesday, October 28, 2015 8:07 PM

Answers

  • I wrote the VBA for my co-workers to transpose their monthly report. You need to modify it. If you want to run it in C# then you need to convert it as well like this

    https://chanmingman.wordpress.com/2015/07/12/the-key-objects-you-should-be-aware-of-when-migrating-vba-to-vsto-c/.

    The code below is only doing transpose from rows to columns. It is neither data access to sql nor write to txt file.

    Public Sub RunDetermineDate()
    
      Dim rng1 As Range
      'Set the activecell to Range
      Set rng1 = ActiveCell
      
      'Check if the cell is not empty
      Do While Not (IsEmpty(ActiveCell.Value))
        DetermineDate (ActiveCell.Value)
        'Move on cell down
        rng1.Offset(1, 0).Select
        'reassign ActiveCell to Range
        Set rng1 = ActiveCell
      Loop
    
    End Sub
    
    Function TransformData(ByVal MyValue)
    
      'Range("L9").Value = "Work"
      Dim rng As Range
      Dim NumRows As Integer
      Dim i As Integer
      'rng = MyValue
      Set rng = ActiveCell
      
      'Range("C5").Select
      
      NumRows = Range(rng.Address, Range(rng.Address).End(xlDown)).Rows.Count
      
      'Do While Not (IsEmpty(ActiveCell.Value))
      For i = 1 To NumRows
        If (ActiveCell.Value = MyValue) Then
          Debug.Print (ActiveCell.Value)
          ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 0).Value
          ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(1, 0).Value
          ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(2, 0).Value
          ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(3, 0).Value
          ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(4, 0).Value
          'Exit Do
          ActiveCell.Offset(1, 2).Value = ActiveCell.Offset(0, 1).Value
          ActiveCell.Offset(1, 3).Value = ActiveCell.Offset(1, 1).Value
          ActiveCell.Offset(1, 4).Value = ActiveCell.Offset(2, 1).Value
          ActiveCell.Offset(1, 5).Value = ActiveCell.Offset(3, 1).Value
          ActiveCell.Offset(1, 6).Value = ActiveCell.Offset(4, 1).Value
        End If
        
        ActiveCell.Offset(1, 0).Select
      'Loop
      Next i
      
      TransformData = MyValue
    End Function
    chanmm


    chanmm


    • Edited by chanmmMVP Thursday, October 29, 2015 1:25 AM
    • Marked as answer by JK95 Thursday, October 29, 2015 7:28 PM
    Thursday, October 29, 2015 1:24 AM