Michael Sterling
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.E xcel.Range CR = (Microsoft.Office.Interop. Excel.Rang e)xlWorkSh eet.Cells[ 1, 1];
to
Microsoft.Office.Interop.E xcel.Range CR = (Microsoft.Office.Interop. Excel.Rang e)xlWorkSh eet.Cells[ 1, 0];
and
Microsoft.Office.Interop.E xcel.Range CR = (Microsoft.Office.Interop. Excel.Rang e)xlWorkSh eet.Cells[ 0, 1];
but then it just breaks the method and nothing happens.
Microsoft.Office.Interop.E
to
Microsoft.Office.Interop.E
and
Microsoft.Office.Interop.E
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);
}
}
}
Perhaps the data that you are pasting has a blank line at the top
ASKER
It must be your copyAlltoClipboard method that includes the column to the left of the range you wish to copy and paste.
/gustav
/gustav
ASKER
@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);
}
I can't tell, but apparently SelectAll graps all columns and that may include a leading (hidden) column.
/gustav
/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?
ASKER
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"Ganador ganador cena de pollo!" Thank you that was what I needed to do! Would never have thought to look at that!