-
February 8th, 2011, 05:34 AM
#1
c# Writing datatable to excel
I can open my workbook and copy my selected sheets within it, rename them and insert data, however not all of the data is inserted, its misses the data in one column. It includes the column title but not the data it holds. Every other column works fine but I can't understand why it doesn't work. The code I have is:
Code:
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;
// Get a new workbook.
string location = @"C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\Tourny Manager (Export Test)\Test.xls";
oWB = oXL.Workbooks.Open(location, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Process the DataTable
// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
DataTable dt = this.tournyManagerDataSet.Tournament;
try
{
dt.Columns.Remove("England Squash Number");
dt.Columns.Remove("Home Phone");
dt.Columns.Remove("Mobile Phone");
dt.Columns.Remove("Email");
dt.Columns.Remove("Email 2");
dt.Columns.Remove("T-Shirt Size");
dt.Columns.Remove("Position");
}
catch
{
}
DataRow[] Rows;
int rowCount;
//BU19
Rows = dt.Select("[Age Group] = 'BU19' AND [Tournament] = '" + tChoice.Text + "'");
if (BU19.Text == "8 Monrad")
{
// Get the active sheet
oSheet = (Excel.Worksheet)oWB.Sheets["8 M"];
oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
oSheet = (Excel.Worksheet)oWB.Sheets["8 M (2)"];
oSheet.Name = "BU19";
rowCount = 1;
foreach (DataRow dr in Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 115],
oSheet.Cells[rowCount, dt.Columns.Count + 115]);
oRange.EntireColumn.AutoFit();
}
else if (BU19.Text == "16 Monrad")
{
// Get the active sheet
oSheet = (Excel.Worksheet)oWB.Sheets["16 M"];
oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
oSheet = (Excel.Worksheet)oWB.Sheets["16 M (2)"];
oSheet.Name = "BU19";
rowCount = 1;
foreach (DataRow dr in Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 115],
oSheet.Cells[rowCount, dt.Columns.Count + 115]);
oRange.EntireColumn.AutoFit();
}
else if (BU19.Text == "32 Monrad")
{
// Get the active sheet
oSheet = (Excel.Worksheet)oWB.Sheets["32 M"];
oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
oSheet = (Excel.Worksheet)oWB.Sheets["32 M (2)"];
oSheet.Name = "BU19";
rowCount = 1;
foreach (DataRow dr in Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 115],
oSheet.Cells[rowCount, dt.Columns.Count + 115]);
oRange.EntireColumn.AutoFit();
}
That is the consistent code throughout it. It is repeated as I don't know a better way to work it, but all bar copying the last columns data works fine.
Thanks,
-
February 8th, 2011, 07:25 PM
#2
Re: c# Writing datatable to excel
Don't worry, found the problem. It was because when testing other queries the column had been missed out somehow so wasn't being copied into the datatable dt
Its taken me all day to narrow down the problem to that one step but I've eventually got there
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|