Link to home
Start Free TrialLog in
Avatar of Michael Sterling
Michael SterlingFlag for United States of America

asked on

Why does my excel data start at B1 instead of A1.

I'm using the following method to export data into an .xlsx (excel spreadsheet), but for some reason the sheet starts the data at B1 instead of A1. How do I get the data to star at A1? I've tried changing the coordinates in this line:

Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];

to

Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 0];

and

Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[0, 1];

but then it just breaks the method and nothing happens.

        //' ***********************************************************
        //' btnExportToExcel_Click                                    *
        //' ***********************************************************
        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            if (dGrdVw.DataSource != null)
            {
                try
                {
                    copyAlltoClipboard();
                    Microsoft.Office.Interop.Excel.Application xlexcel;
                    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                    object misValue = System.Reflection.Missing.Value;
                    xlexcel = new Microsoft.Office.Interop.Excel.Application();
                    xlexcel.Visible = true;
                    xlWorkBook = xlexcel.Workbooks.Add(misValue);
                    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
                    CR.Select();
                    xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "FAILED TO EXPORT TO EXCEL!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

Open in new window

Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Perhaps the data that you are pasting has a blank line at the top
Avatar of Michael Sterling

ASKER

@Shaun: If that were the case, wouldn't that mean I would have on blank horizontal row, and then the next row would / should still start at A2?

User generated image
User generated image
It must be your copyAlltoClipboard  method that includes the column to the left of the range you wish to copy and paste.

/gustav
@Gustav: I'm not sure what would cause that in this function/method. Thoughts?

        private void copyAlltoClipboard()
        {
            dGrdVw.SelectAll();
            DataObject dataObj = dGrdVw.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }

Open in new window

I can't tell, but apparently SelectAll graps all columns and that may include a leading (hidden) column.

/gustav
The first column seems exported with blank values could be due to the row selector... I guess you can hide it by setting the property RowHeadersVisible to false and try again?
@Ryan: I'm confused / can't make the connection on how setting the RowHeaderVisible to false will get rid of the blank column? Can you explain further?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"Ganador ganador cena de pollo!" Thank you that was what I needed to do! Would never have thought to look at that!