|
try this
#Region "WriteXMLFile"<br />
<br />
''' <summary><br />
''' Schreibt das XML File<br />
''' </summary><br />
''' <param name="Source"></param><br />
''' <param name="fileName"></param><br />
''' <remarks></remarks><br />
Private Shared Sub WriteXMLFile(ByRef Source As DataSet, ByVal fileName As String)<br />
'http://www.codeproject.com/dotnet/ExportToExcel.asp?df=100&forumid=146533&exp=0&select=1357703<br />
<br />
Dim excelDoc As System.IO.StreamWriter<br />
excelDoc = New System.IO.StreamWriter(fileName)<br />
<br />
'
Dim startExcelXML As String = "<?xml version=""1.0""?>" + vbCrLf + "<Workbook " + "xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" + vbCrLf + " xmlns:o=""urn:schemas-microsoft-com:office:office""" + vbCrLf + " " + "xmlns:x=""urn:schemas-microsoft-com:office:" + "excel""" + vbCrLf + " xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">" + vbCrLf + " <Styles>" + vbCrLf + " " + "<Style ss:ID=""Default"" ss:Name=""Normal"">" + vbCrLf + " " + "<Alignment ss:Vertical=""Bottom""/>" + vbCrLf + " <Borders/>" + "" + vbCrLf + " <Font/>" + vbCrLf + " <Interior/>" + vbCrLf + " <NumberFormat/>" + vbCrLf + " <Protection/>" + vbCrLf + " </Style>" + vbCrLf + " " + "<Style ss:ID=""BoldColumn"">" + vbCrLf + " <Font " + "x:Family=""Swiss"" ss:Bold=""1""/>" + vbCrLf + " </Style>" + vbCrLf + " " + "<Style ss:ID=""StringLiteral"">" + vbCrLf + " <NumberFormat" + " ss:Format=""@""/>" + vbCrLf + " </Style>" + vbCrLf + " <Style " + "ss:ID=""Decimal"">" + vbCrLf + " <NumberFormat " + "ss:Format=""0.0000""/>" + vbCrLf + " </Style>" + vbCrLf + " " + "<Style ss:ID=""Integer"">" + vbCrLf + " <NumberFormat " + "ss:Format=""0""/>" + vbCrLf + " </Style>" + vbCrLf + " <Style " + "ss:ID=""DateLiteral"">" + vbCrLf + " <NumberFormat " + "ss:Format=""mm/dd/yyyy;@""/>" + vbCrLf + " </Style>" + vbCrLf + " " + "</Styles>" + vbCrLf + " "<br />
Dim endExcelXML As String = "</Workbook>"<br />
Dim rowCount As Integer = 0<br />
Dim sheetCount As Integer = 1<br />
<br />
excelDoc.Write(startExcelXML)<br />
excelDoc.WriteLine("<Worksheet ss:Name=""Sheet" + sheetCount.ToString() + """>")<br />
excelDoc.WriteLine("<Table>")<br />
excelDoc.WriteLine("<Row>")<br />
<br />
'//Hier die einzelnen Columns (fett gedruckt)<br />
Dim z As Integer = 0<br />
Do While (z < Source.Tables(0).Columns.Count)<br />
excelDoc.WriteLine("<Cell ss:StyleID=""BoldColumn""><Data ss:Type=""String"">")<br />
excelDoc.Write(Source.Tables(0).Columns(z).ColumnName)<br />
excelDoc.Write("</Data></Cell>")<br />
z = z + 1<br />
Loop<br />
excelDoc.Write("</Row>")<br />
<br />
'
For Each x As DataRow In Source.Tables(0).Rows<br />
rowCount = rowCount + 1<br />
'if the number of rows is > 64000 create a new page to continue output<br />
If (rowCount = 64000) Then<br />
rowCount = 0<br />
sheetCount = (sheetCount + 1)<br />
excelDoc.WriteLine("</Table>")<br />
excelDoc.WriteLine(" </Worksheet>")<br />
excelDoc.WriteLine("<Worksheet ss:Name=""Sheet" + sheetCount.ToString() + """>")<br />
excelDoc.Write("<Table>")<br />
End If<br />
excelDoc.Write("<Row>")<br />
<br />
Dim y As Integer = 0<br />
Dim sb As New System.Text.StringBuilder()<br />
Dim xxx As New XmlTextWriter(New System.IO.StringWriter(sb))<br />
Do While (y < Source.Tables(0).Columns.Count)<br />
Dim rowType As System.Type<br />
rowType = x(y).GetType<br />
Select Case (rowType.ToString)<br />
Case "System.String"<br />
Dim XMLstring As String = x(y).ToString<br />
XMLstring = XMLstring.Trim<br />
XMLstring = XMLstring.Replace("&", "&")<br />
XMLstring = XMLstring.Replace(">", ">")<br />
XMLstring = XMLstring.Replace("<", "<")<br />
<br />
<br />
sb = New System.Text.StringBuilder()<br />
xxx = New XmlTextWriter(New System.IO.StringWriter(sb))<br />
xxx.WriteString(XMLstring)<br />
xxx.Flush()<br />
XMLstring = sb.ToString()<br />
<br />
<br />
excelDoc.WriteLine("<Cell ss:StyleID=""StringLiteral"">" + "<Data ss:Type=""String"">")<br />
excelDoc.Write(XMLstring)<br />
excelDoc.Write("</Data></Cell>")<br />
Case "System.DateTime"<br />
'Excel has a specific Date Format of YYYY-MM-DD followed by <br />
'the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000<br />
'The Following Code puts the date stored in XMLDate <br />
'to the format above<br />
Dim XMLDate As DateTime = CType(x(y), DateTime)<br />
Dim XMLDatetoString As String = ""<br />
'Excel Converted Date<br />
XMLDatetoString = XMLDate.Year.ToString + "-" + XMLDate.Month < 10<br />
excelDoc.WriteLine("<Cell ss:StyleID=""DateLiteral"">" + "<Data ss:Type=""DateTime"">")<br />
excelDoc.Write(XMLDatetoString)<br />
excelDoc.Write("</Data></Cell>")<br />
Case "System.Boolean"<br />
excelDoc.WriteLine("<Cell ss:StyleID=""StringLiteral"">" + "<Data ss:Type=""String"">")<br />
excelDoc.Write(x(y).ToString)<br />
excelDoc.Write("</Data></Cell>")<br />
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"<br />
excelDoc.WriteLine("<Cell ss:StyleID=""Integer"">" + "<Data ss:Type=""Number"">")<br />
excelDoc.Write(x(y).ToString)<br />
excelDoc.Write("</Data></Cell>")<br />
Case "System.Decimal", "System.Double"<br />
excelDoc.WriteLine("<Cell ss:StyleID=""Decimal""><Data ss:Type=""Number"">")<br />
excelDoc.Write(x(y).ToString)<br />
excelDoc.Write("</Data></Cell>")<br />
Case "System.DBNull"<br />
excelDoc.WriteLine("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")<br />
excelDoc.Write("")<br />
excelDoc.Write("</Data></Cell>")<br />
Case Else<br />
Throw New Exception((rowType.ToString + " not handled."))<br />
End Select<br />
y = y + 1<br />
Loop<br />
excelDoc.WriteLine("</Row>")<br />
Next<br />
<br />
excelDoc.WriteLine("</Table>")<br />
excelDoc.WriteLine(" </Worksheet>")<br />
excelDoc.Write(endExcelXML)<br />
excelDoc.Close()<br />
End Sub<br />
#end region
|
|
|
|
|
The Above code works Fine For Office 2003 but Not working For office 2000
What will be the reason.
|
|
|
|
|
The VB.Net version accidentally ommitted some key data. :o (colon followed by a 'o') is actually a smiley face on this site. Here is an explanation..
This is the first part of what the output from the xml/xls file should look like:
<br />
<?xml version="1.0"?><br />
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"<br />
xmlns:o="urn:schemas-microsoft-com:office:office"<br />
xmlns:x="urn:schemas-microsoft-com:office:excel"<br />
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"><br />
but the code was putting out:
<br />
<xml version><br />
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"<br />
xmlns="urn:schemas-microsoft-com:office:office"<br />
xmlns:x="urn:schemas-microsoft-com:office:excel"<br />
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"><br />
The second xmls was missing the :o after it and there are some version issues that might cause Parse problems with some versions of Excel
Here is a working copy that avoids the Strict Parse problem:
'Create the StreamWriter to the specified file.
Dim excelDoc As New StreamWriter(FileName)
'Create the Workbood Header.
Const startExcelXML As String = "<?xml version='1.0'?>" + vbCrLf + _
"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" + vbCrLf + _
"xmlns:o=""urn:schemas-microsoft-com:office:office""" + vbCrLf + _
"xmlns:x=""urn:schemas-microsoft-com:office:excel""" + vbCrLf + _
"xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">" + vbCrLf + _
"<Styles>" + vbCrLf + _
"<Style ss:ID=""Default"" ss:Name=""Normal"">" + vbCrLf + _
"<Alignment ss:Vertical=""Bottom""/>" + vbCrLf + _
"<Borders/>" + vbCrLf + _
"<Font/>" + vbCrLf + _
"<Interior/>" + vbCrLf + _
"<NumberFormat/>" + vbCrLf + _
"<Protection/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"<Style ss:ID=""BoldColumn"">" + vbCrLf + _
"<Font x:Family=""Swiss"" ss:Bold=""1""/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"<Style ss:ID=""StringLiteral"">" + vbCrLf + _
"<NumberFormat ss:Format=""@""/>" + vbCrLf + _
"<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"<Style ss:ID=""Decimal"">" + vbCrLf + _
"<NumberFormat ss:Format=""0.0000""/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"<Style ss:ID=""Integer"">" + vbCrLf + _
"<NumberFormat ss:Format=""0""/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"<Style ss:ID=""DateLiteral"">" + vbCrLf + _
"<NumberFormat ss:Format=""mm/dd/yyyy;@""/>" + vbCrLf + _
"</Style>" + vbCrLf + _
"</Styles>" + vbCrLf
'Const startExcelXML As String = "<xml version>" & vbCr & vbLf & "<Workbook " & "xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCr & vbLf & " xmlns=""urn:schemas-microsoft-com:office:office""" & vbCr & vbLf & " " & "xmlns:x=""urn:schemas- microsoft-com:office:" & "excel""" & vbCr & vbLf & " xmlns:ss=""urn:schemas-microsoft-com:" & "office:spreadsheet"">" & vbCr & vbLf & " <Styles>" & vbCr & vbLf & " " & "<Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCr & vbLf & " " & "<Alignment ss:Vertical=""Bottom""/>" & vbCr & vbLf & " <Borders/>" & vbCr & vbLf & " <Font/>" & vbCr & vbLf & " <Interior/>" & vbCr & vbLf & " <NumberFormat/>" & vbCr & vbLf & " <Protection/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""BoldColumn"">" & vbCr & vbLf & " <Font " & "x:Family=""Swiss"" ss:Bold=""1""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""StringLiteral"">" & vbCr & vbLf & " <NumberFormat" & " ss:Format=""@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""Decimal"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0.0000""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "<Style ss:ID=""Integer"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""0""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " <Style " & "ss:ID=""DateLiteral"">" & vbCr & vbLf & " <NumberFormat " & "ss:Format=""mm/dd/yyyy;@""/>" & vbCr & vbLf & " </Style>" & vbCr & vbLf & " " & "</Styles>" & vbCr & vbLf & " "
'Const endExcelXML As String = "</Workbook>"
'Create the workbook footer
Const endExcelXML As String = "</Workbook>"
Dim rowCount As Integer = 0
Dim SheetCount As Integer = 1
'Begin Workbook Output.
excelDoc.Write(startExcelXML)
excelDoc.Write("<Worksheet ss:Name=""Sheet" + SheetCount.ToString + """>")
excelDoc.Write("<Table>" + vbCrLf)
'Set the column widths
For Each Column As DataGridViewColumn In DataGridView.Columns
If Column.Visible = True Then
excelDoc.Write("<Column ss:Width=""" + Column.Width.ToString + """/>")
End If
Next
excelDoc.Write("<Row>" + vbCrLf)
'Write the column Headers.
For Each column As DataGridViewColumn In DataGridView.Columns
If column.Visible = True Then
excelDoc.Write("<Cell ss:StyleID=""BoldColumn""><Data ss:Type=""String"">")
excelDoc.Write(column.HeaderText)
excelDoc.Write("</Data></Cell>" + vbCrLf)
End If
Next
excelDoc.Write("</Row>" + vbCrLf)
'Export the DataGrid Row Data.
For Each Row As DataGridViewRow In DataGridView.Rows
rowCount += 1
If rowCount > 64000 Then
rowCount = 0
SheetCount += 1
excelDoc.Write("</Table>")
excelDoc.Write("</WorkSheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet""" + SheetCount.ToString + ">")
End If
excelDoc.Write("<Row>")
For Each cell As DataGridViewCell In Row.Cells
If cell.Visible = True Then
Select Case cell.ValueType.Name
Case "String"
Dim XMLString As String = cell.FormattedValue.ToString
XMLString = XMLString.Trim
XMLString = XMLString.Replace("&", "&")
XMLString = XMLString.Replace(">", ">")
XMLString = XMLString.Replace("<", "<")
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write(XMLString)
excelDoc.Write("</Data></Cell>")
Case "DateTime"
Dim XMLDate As DateTime
Dim xmlDateString As String = ""
If Not IsDBNull(cell.Value) Then
XMLDate = CType(cell.Value, Date)
xmlDateString = XMLDate.Year.ToString
xmlDateString += "-"
xmlDateString += CType(IIf(XMLDate.Month < 10, "0" & XMLDate.Month.ToString, XMLDate.Month.ToString), String)
xmlDateString += "-"
xmlDateString += CType(IIf(XMLDate.Day < 10, "0" & XMLDate.Day.ToString, XMLDate.Day.ToString), String)
xmlDateString += "T"
xmlDateString += CType(IIf(XMLDate.Hour < 10, "0" & XMLDate.Hour.ToString, XMLDate.Hour.ToString), String)
xmlDateString += ":"
xmlDateString += CType(IIf(XMLDate.Minute < 10, "0" & XMLDate.Minute.ToString, XMLDate.Minute.ToString), String)
xmlDateString += ":"
xmlDateString += CType(IIf(XMLDate.Second < 10, "0" & XMLDate.Second.ToString, XMLDate.Second.ToString), String)
xmlDateString += ".000"
excelDoc.Write("<Cell ss:StyleID=""DateLiteral""><Data ss:Type=""DateTime"">")
excelDoc.Write(xmlDateString)
excelDoc.Write("</Data></Cell>")
Else
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
End If
Case "Boolean"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""Number"">")
excelDoc.Write(cell.FormattedValue.ToString)
excelDoc.Write("</Data></Cell>")
Case "Integer", "Int16", "Int32", "Int64"
If cell.OwningColumn.CellType.Name = "DataGridViewComboBoxCell" Then
Dim XMLString As String = cell.FormattedValue.ToString
XMLString = XMLString.Trim
XMLString = XMLString.Replace("&", "&")
XMLString = XMLString.Replace(">", ">")
XMLString = XMLString.Replace("<", "<")
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write(XMLString)
excelDoc.Write("</Data></Cell>")
ElseIf cell.OwningColumn.CellType.Name = "DataGridViewCheckBoxCell" Then
Dim ValueString As String = Nothing
Select Case cell.Value.ToString
Case "0"
ValueString = "False"
Case "1"
ValueString = "True"
Case Else
ValueString = "Unknown"
End Select
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write(ValueString)
excelDoc.Write("</Data></Cell>")
Else
excelDoc.Write("<Cell ss:StyleID=""Integer""><Data ss:Type=""Number"">")
excelDoc.Write(cell.Value)
excelDoc.Write("</Data></Cell>")
End If
Case "Decimal", "Double", "Single"
excelDoc.Write("<Cell ss:StyleID=""Decimal""><Data ss:Type=""Number"">")
excelDoc.Write(cell.Value)
excelDoc.Write("</Data></Cell>")
Case "Byte"
Dim ValueString As String = Nothing
Select Case cell.Value.ToString
Case "0"
ValueString = "False"
Case "1"
ValueString = "True"
Case Else
ValueString = "Unknown"
End Select
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write(ValueString)
excelDoc.Write("</Data></Cell>")
Case "CheckState"
Dim ValueString As String = Nothing
Select Case cell.Value.ToString
Case "0"
ValueString = "False"
Case "1"
ValueString = "True"
Case Else
ValueString = "Unknown"
End Select
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""><Data ss:Type=""String"">")
excelDoc.Write(ValueString)
excelDoc.Write("</Data></Cell>")
Case "DBNull"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral""></Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
Case Else
Throw New Exception(cell.ValueType.ToString + ": Not Handled")
End Select
excelDoc.Write(vbCrLf)
End If
Next
excelDoc.Write("</Row>" + vbCrLf)
Next
'Close out the workBook.
excelDoc.Write("</Table>" + vbCrLf)
excelDoc.Write(" </Worksheet>" + vbCrLf)
excelDoc.Write(endExcelXML)
excelDoc.Close()
Hipe this helps anyone using the VB version posted here
|
|
|
|
|
Hi,
Thanks for the translation. I have a problem like this has occurred. Turkish currency is different from the transfer is meaningless.grid using decimal format. decimal format when the transfer happens.
DatagridView Format
319.759,71
Excel Result
31975971,0000
Help me please. did not try the method
|
|
|
|
|
I having tough time with converting the above C# to Vb.Net..Could anyone please convert the above code to VB.NET.
Thanks for ur help in advance..
sdfasdf
|
|
|
|
|
http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx
this might help you. Its better if you try it yourself, you'll only get better.
|
|
|
|
|
I get a "Workbook Setting" error when I use that code coverter. See my code elsewhere inthese posts.
|
|
|
|
|
Hi,
Could you tell me how to apply style to cell.
Actually i am exporting html table to Excel, i want to have some cell to be in gray color.
it would be great helpful for me.
thanks alot.
|
|
|
|
|
Here is the best way to do that,
- Go into excel and create a cell with a gray background
- Save the excel document as .xml
- open the xml in a text editor
- there should be an area inside of the xml document that defines the style of the cell with the gray background
- after finding the style, its a matter of translating the html to xml (this should be fairly simple, just a bit of parsing)
hope that helps
look for something that looks like the following
<Style ss:ID="s23">
<Font/>
<Interior ss:Color="#808080" ss:Pattern="Solid"/>
</Style>
-- modified at 12:44 Thursday 29th June, 2006
|
|
|
|
|
I am trying toi export data into excel using an asp application. I have handled the paging within the code so that id the records returned increase 55000 they are inserted into a new worksheet. But all goes fine untill i get a dataset of records greater than 64000. The page just hangs ??? any clue why this is so...
Arsalan
|
|
|
|
|
thts may b the max size of a excel worksheet is 65k .....
i have a prob as my dataset can return more than 65k records ....
any soln for making it in one sheet ????
|
|
|
|
|
Hi,
This is a very useful way of generating Excel sheets as there is option if having multiple worksheets and font colours and size can also be given.
I have used the above method to generate excel file. But in some machines the file opens as as XML document [with all the tags visible]. How can it be solved.
Is there any way to convert the file XML-Excel generated to be programatically converted to Binary-Excel file without using COM?
Thanks
Vikky.
|
|
|
|
|
there is actually, there is a thread a couple of lines down that shows how to put it in a binary format without the use of com.
|
|
|
|
|
Hi,
I tried the option but was not able to have multiple worksheets in it; also the font size and colors cannot be set.
Hence any help in creating multiple work sheets in binary-excel without COM will be greatly appreciated.
Thanks,
Vikky.
|
|
|
|
|
Where is this "thread a couple of lines down" ..? A couple lines down from *where*?
|
|
|
|
|
Thanks for the code.
I have created an XML document. It opens in Explorer and looks the way XML should. The formatting issue comes when I open it (sample.xml) in Excel 2000. All the values are concatenated into row0,col0.
What am I missing?
Thanks
TuneFish
|
|
|
|
|
does your data have any less than or greater than signs? that might be cause an issue in formating. the way i figured out how to format the file was to simply go into excel and type out the data how i wanted it. i would then save the file in xml format, and see what i came up with. you might want to check that version with the one created by your code to see if there is a difference. let me know if you need any more help
|
|
|
|
|
The solution given in this article generates an xml file which is understood by excel application. It doesn't generates a excel file in binary format. To do so you need to do "Save as" while file is open in excel application, and choose appropriate file type.
If you want to generate excel-binary file programaticaly then here is the code. Just call exportToExcel(dataset, filePath), where dataset is the DataSet containing data for which excel file has to be generated, and filePath is the string containing complete filePath where the file should be generated.
using System;<br />
using System.IO;<br />
using System.Collections;<br />
using System.Data;<br />
using System.Text;<br />
using System.Data.OleDb;<br />
<br />
private static OleDbConnection GetConnection(string filePath ) <br />
{<br />
return new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"");<br />
}<br />
<br />
public static void exportToExcel(DataTable dt, string fileName)<br />
{<br />
#region initialization<br />
System.IO.FileInfo fi = new System.IO.FileInfo(fileName);<br />
if (fi.Exists)<br />
{<br />
fi.Delete();<br />
}<br />
<br />
if (dt.TableName == string.Empty)<br />
{<br />
dt.TableName = "Sheet1";<br />
}<br />
else
{<br />
dt.TableName = dt.TableName.Replace("$",string.Empty);<br />
}<br />
#endregion<br />
<br />
<br />
string sql = GetTableCreationSql(dt);<br />
OleDbConnection connection = GetConnection(fileName);<br />
OleDbCommand command = new OleDbCommand();<br />
command.Connection = connection;<br />
try<br />
{<br />
#region Create table<br />
command.CommandText = GetTableCreationSql(dt);<br />
command.Connection.Open();<br />
command.ExecuteNonQuery();<br />
#endregion<br />
<br />
#region Insert Into Table<br />
<br />
command.CommandText = GetInsertSql(dt);<br />
<br />
foreach(DataRow row in dt.Rows)<br />
{<br />
SetParametersInCommand(row, command);<br />
command.ExecuteNonQuery();<br />
}<br />
<br />
#endregion<br />
}<br />
finally<br />
{<br />
command.Connection.Close();<br />
}<br />
<br />
}<br />
<br />
<br />
<br />
private static string GetTableCreationSql(DataTable dt)<br />
{<br />
StringBuilder sqlBuilder = new StringBuilder();<br />
sqlBuilder.Append("create table [");<br />
sqlBuilder.Append(dt.TableName);<br />
sqlBuilder.Append( "] ( " );<br />
<br />
#region Get Column List<br />
foreach(DataColumn col in dt.Columns)<br />
{<br />
if (col.Ordinal== 0)<br />
sqlBuilder.Append( "[" );<br />
else<br />
sqlBuilder.Append( ", [" );<br />
<br />
sqlBuilder.Append(col.ColumnName.Trim());<br />
sqlBuilder.Append("] nvarchar");<br />
}<br />
#endregion<br />
<br />
sqlBuilder.Append(" )"); <br />
<br />
return sqlBuilder.ToString();<br />
}<br />
<br />
private static string GetInsertSql(DataTable dt)<br />
{<br />
StringBuilder sqlBuilder = new StringBuilder();<br />
StringBuilder paramMarks = new StringBuilder();<br />
sqlBuilder.Append("Insert Into [");<br />
sqlBuilder.Append(dt.TableName);<br />
sqlBuilder.Append( "] ( " );<br />
<br />
<br />
#region Get Column List<br />
foreach(DataColumn col in dt.Columns)<br />
{<br />
if (col.Ordinal == 0)<br />
{<br />
sqlBuilder.Append( "[" );<br />
paramMarks.Append( "?" );<br />
}<br />
else<br />
{<br />
sqlBuilder.Append( ", [");<br />
paramMarks.Append(",?");<br />
}<br />
<br />
sqlBuilder.Append(col.ColumnName.Trim());<br />
sqlBuilder.Append( "] " );<br />
}<br />
#endregion<br />
<br />
sqlBuilder.Append(" ) values ("); <br />
sqlBuilder.Append(paramMarks.ToString() );<br />
sqlBuilder.Append( ")" );<br />
<br />
return sqlBuilder.ToString();<br />
}<br />
<br />
<br />
private static void SetParametersInCommand(DataRow row, OleDbCommand command)<br />
{<br />
UnicodeEncoding en = new UnicodeEncoding();<br />
string argumentName= string.Empty;<br />
int index = 0;<br />
command.Parameters.Clear();<br />
foreach(DataColumn col in row.Table.Columns)<br />
{<br />
argumentName = "@Args" + index;<br />
command.Parameters.Add( new OleDbParameter(argumentName, OleDbType.VarBinary)).Value = <br />
en.GetBytes(row[ col ].ToString());<br />
index++;<br />
} <br />
}
Sanjeev Kumar Singh,(sanjeevofbcs@hotmail.com)
modified on Friday, April 17, 2009 4:51 AM
|
|
|
|
|
Hi Sanjeev,
Is there any method by which the cell can have font colour and size as well.
Also if the dataset has multiple tables are they stored in diffrent worksheets in Excel document?
Thanks
Vikky.
|
|
|
|
|
Hi Vikky,
1> I don't know a way to apply formating for font color and size.
2> If the dataset has multiple tables then you will need to slightly change the code. For each datatable in DataSet call GetTableCreationSql(DataTable dt) and GetInsertSql(DataTable dt) functions. It will add different sheets for different tables in DataSet.
~Sanjeev.
|
|
|
|
|
The above code works well except for an extra ' in each Excel column. Where does this extra ' come from?
Header: 'EMPLOYEE ID
Row: ' 101922
|
|
|
|
|
This is exactly what I was looking for. It works great. I only wish there was a way to format the columns.
Thanks.
Ed
|
|
|
|
|
great and useful code, btw it seems to me there is an error, in SetParametersInCommand parameters are added to the command object, but the list is not cleared, so the insert query only addes repetively the first row, just add a command.Parameters.Clear(); before the foreach and it's ok.
thanks a LOT
|
|
|
|
|
Mike,You are right. I had updated the code. Thanks for pointing it out.
modified on Friday, April 17, 2009 5:44 AM
|
|
|
|
|
Excellent it works flawlessly. Bear in mind though that JET does not support 64bits so if you planning on developing 64bits apps then use the XML version instead.
Thanks again for the solutions guys
|
|
|
|