|
You need to add code to clear Parameters to reset it for each row.
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 />
}
|
|
|
|
|
Alright guys, a small error in the code. Note: it was correct until it became not just a user submission. Here is the error, the error occured because of html conversion.
Its in the System.String case statement. Please be sure to change it. Also, performance overall is improved if you combine all the excelDoc.Write() commands under each case statment...helps out alot.
<br> case "System.String":<br> string XMLstring = x[y].ToString();<br> XMLstring = XMLstring.Trim();<br> XMLstring = XMLstring.Replace("&","&");<br> XMLstring = XMLstring.Replace(">",">");<br> XMLstring = XMLstring.Replace("<","<");<br> excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + <br> "<Data ss:Type=\"String\">");<br> excelDoc.Write(XMLstring);<br> excelDoc.Write("</Data></Cell>");<br> break;<br>
|
|
|
|
|
Hi all and greetings
I have a problem to export in excel with this great code.
I obtain a blank worksheet
I have a complex query where results come from different tables and view on database.
Database is MySql version 5.0
At the end of my query, i've try to do the follow:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataSet ds = new DataSet();
adapter.Fille(ds, "Here I Dont Know what write, cause i dont have a single table");
exportToExcel(ds, @"c:\myFile.xls");
[/code]
My complex query, works perfectly to view data on a datagridview in this way:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataTable dt = new DataTable();
adapter.Fille(dt);
datagridview.datasource = dt;
[/code]
I've already tryed to add the table to dataset:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
adapter.Fille(dt);
ds.Tables.Add(dt);
exportToExcel(ds, @"c\:myFile.xls");
[/code]
This give me a blank file too.
Thx in advance.
Raoul.
|
|
|
|
|
i am unfamiliar with the MySqlDataAdapter class, but you might want to try creating a Temporary table at the begin of the query, insert into the Temporary table and then Select * from Temporary Table. but bottom line is that if you can bind that dataset returned to a datagrid then you should be able to export it. Start by trying to bind it to a datagrid. if you can do that then just step through the my export code and see where its messing up. you might also want to check the file in notepad and see if its writing anything into the file at all. also, remeber that excel is limited to 255 columns so if your query returns more than 255 columns it may create problems.
-let me know how things go
|
|
|
|
|
Many thx for reply
But... it still going bad
here is the code:
<br />
string select = "SELECT dip.Surname,dip.Name";<br />
<br />
for (int count = 1; count <= days; ++count)<br />
{<br />
select += ",MAX(CASE WHEN ISNULL(IF(monthlyview.day='" + count + "', DATE_FORMAT(monthlyview.total, '%H.%i'), '')) THEN monthlyview.nothing WHEN ISNULL(monthlyview.nothing) THEN IF(monthlyview.day = '" + count + "' ,DATE_FORMAT(monthlyview.total, '%H.%i'), '') END) AS '" + count + "'";<br />
}<br />
<br />
<br />
select += ",DATE_FORMAT(globalmonth.total, '%H.%i') AS 'GrandTotal' FROM dip, hbeta, monthlyview, globalmonth " +<br />
"WHERE monthlyview.ID_Dip = dip.ID_Dipendente " +<br />
"AND monthlyview.ID_Dip = globalmese.ID_Dip " +<br />
"AND monthlyview.ID_Month = '" + ((DateTimePicker)dateTimePickerHost.Control).Value.Month + "' " +<br />
"GROUP BY globalmese.ID_Dip";<br />
MySqlDataAdapter mda = new MySqlDataAdapter(select, cn);<br />
DataSet ds = new DataSet();<br />
DataTable dt = new DataTable();<br />
mda.Fill(dt);<br />
ds.Tables.Add(dt);<br />
dataGridView1.DataSource = ds;<br />
dataGridView1.DataMember = dt.ToString();<br />
exportToExcel(ds, "c:\\test.xls");<br />
This code works perfectly in program; he give me expected results on datagridview.
About number of columns, number cant be more of 32.
Im still having a blank excel sheet.
Thank you so much
Another little edit:
If i open the file with notepad,as your suggets, i could read data in it
-- modified at 7:19 Thursday 21st September, 2006
|
|
|
|
|
Hi! Cool code! The code has helped me a lot, but I still Have one problem left. I'am exporting my excelfile to a system wher the columns in the Excelfile should be <col1>, <col2>, the Converter does not like the > sign, it causes an error when the file is opened, does anyony know how to handle this?
Thanks /Ola
|
|
|
|
|
thats an error in the article actually, if you look further down the posts, you'll see and "Error in Code" post. Take a look at that, ive tried multiple times to updated it but Codeproject still does not change it.
|
|
|
|
|
Hi, I built two routines to take advantage of this code, one for a data set and one for erach data table inside:
The data set calls:
-------------------------------------------------------------------------------------------------------------------------------------------------------
Const MAX_EXCEL_ROWS As Integer = 65000
Friend Sub ExportDataSetToExcel _
( _
fileName As String, _
tablesToExport As CAT_Data_Table_Abstract() _
) _
IMPLEMENTS CAT_Data_Set_Interface.ExportDataSetToExcel
Const CARRIAGE_RETURN As String = "\r\n "
Const startExcelXML As String = _
"<xml version="">" & CARRIAGE_RETURN & _
"<workbook "="" &="" _
="" "xmlns="urn:schemas-microsoft-com:office:spreadsheet" carriage_return="" "xmlns ="urn:schemas-microsoft-com:office:office" "xmlns:x="urn:schemas-microsoft-com:office:excel" "xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" "<styles="">" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<Alignment ss:Vertical='Bottom'>" & CARRIAGE_RETURN & _
"<Borders/>" & CARRIAGE_RETURN & _
"<Font/>" & CARRIAGE_RETURN & _
"<Interior/>" & CARRIAGE_RETURN & _
"<NumberFormat/>" & CARRIAGE_RETURN & _
"<Protection/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<Font x:Family='Arial' ss:Bold='1'/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<NumberFormat ss:Format='@'/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<NumberFormat ss:Format='0.0000'/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<NumberFormat ss:Format='0'/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"<NumberFormat ss:Format='mm/dd/yyyy;@'/>" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN & _
"" & CARRIAGE_RETURN
Const endExcelXML As String = ""
Dim excelDoc As System.IO.StreamWriter = New System.IO.StreamWriter( fileName )
excelDoc.Write(startExcelXML)
For Each thisTable As CAT_Data_Table_Abstract In tablesToExport
thisTable.ExportDataTableToExcel( excelDoc )
Next
excelDoc.Write(endExcelXML)
excelDoc.Close()
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------
...and the routine called inside runs a sub inside of each data table:
-------------------------------------------------------------------------------------------------------------------------------------------------------
Public Sub ExportDataTableToExcel _
( _
ByRef excelDoc As System.IO.StreamWriter _
) _
IMPLEMENTS CAT_Data_Table_Interface.ExportDataTableToExcel
Dim sheetCount As Integer = 1
With excelDoc
.Write( SheetNameWithCount( sheetCount ) )
.Write( "" )
.Write( "<row>" )
For thisColumn As Integer = 0 To ( Columns.Count ) - 1
.Write( "<cell ss:styleid="BoldColumn">" )
.Write( Columns( thisColumn ).ColumnName )
.Write( "" )
Next thisColumn
.Write( "" )
Dim rowType As System.Type
Dim XMLstring As String
Dim XMLDate As DateTime
Dim XMLDatetoString As String
Dim rowCount As Integer = 0
For Each thisRow As DataRow In Rows
rowCount += 1
' If we hit the bottom of the sheet, create a new page to continue output
If ( rowCount = MAX_EXCEL_ROWS ) Then
rowCount = 0
sheetCount += 1
.Write("")
.Write("")
.Write( SheetNameWithCount( sheetCount ) )
.Write("")
End If
.Write( "<row>" )
For thisColumn As Integer = 0 To Columns.Count - 1
rowType = thisRow( thisColumn ).GetType()
Select Case rowType.ToString()
Case "System.String"
XMLstring = thisRow( thisColumn ).ToString()
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&","&")
XMLstring = XMLstring.Replace(">",">")
XMLstring = XMLstring.Replace("<","<")
.Write( "<cell ss:styleid="StringLiteral">" + "" )
.Write( XMLstring )
.Write( "" )
Case "System.DateTime"
' Excel has a specific Date Format of YYYY-MM-DD followed by the letter 'T', then hh:mm:sss.lll
' Example 2005-01-31T24:01:21.000
XMLDate = CType( thisRow( thisColumn ), DateTime )
'XMLDatetoString = XMLDate.Year.ToString() + "-" +(If XMLDate.Month < 10 Then "0" + XMLDate.Month.ToString() Else XMLDate.Month.ToString()) + "-" +(If XMLDate.Day < 10 Then "0" + XMLDate.Day.ToString() Else XMLDate.Day.ToString()) + "T" +(If XMLDate.Hour < 10 Then "0" + XMLDate.Hour.ToString() Else XMLDate.Hour.ToString()) + ":" +(If XMLDate.Minute < 10 Then "0" + XMLDate.Minute.ToString() Else XMLDate.Minute.ToString()) + ":" +(If XMLDate.Second < 10 Then "0" + XMLDate.Second.ToString() Else XMLDate.Second.ToString()) + ".000"
XMLDatetoString = XMLDate.Year.ToString() & "-" & _
Format( XMLDate.Month, "00" ) & "-" & _
Format( XMLDate.Day, "00" ) & "-" & _
"T" & _
Format( XMLDate.Hour, "00" ) & _
":" & _
Format( XMLDate.Minute, "00" ) & _
":" & _
Format( XMLDate.Second, "00" ) & _
".000"
.Write( "<cell ss:styleid="DateLiteral">" + "" )
.Write( XMLDatetoString )
.Write( "" )
Case "System.Boolean"
.Write( "<cell ss:styleid="StringLiteral">" + "" )
.Write( thisRow( thisColumn ).ToString() )
.Write( "" )
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
.Write( "<cell ss:styleid="Integer">" + "" )
.Write( thisRow( thisColumn ).ToString() )
.Write( "" )
Case "System.Decimal", "System.Double"
.Write( "<cell ss:styleid="Decimal">" + "" )
.Write( thisRow( thisColumn ).ToString())
.Write( "" )
Case "System.DBNull"
.Write( "<cell ss:styleid="StringLiteral">" + "" )
.Write( vbNullString )
.Write( "" )
Case Else
'Throw New Exception( rowType.ToString() + " not handled." )
Debug.WriteLine( "EXPORT DATA TABLE TO EXCEL ->" & TableName & "<->" & rowType.ToString() + "<- not handled." )
End Select
Next thisColumn
.Write("")
Next thisRow
.Write("")
.Write(" ")
End With
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------
I am getting a "Strict Parse Error" at export.
Is this because I converted double quotes to apostrophes in your constants?
Is the \r\n needed in VB?
|
|
|
|
|
i didnt go through your code completely, but see if there are ampersand, less than signs or greater than signs in your data. there is a small error in the code, if you look at the previous posts, you will see one called "Error in code", it'll tell you where the error is. Also \r\n are C# specific, the vb.net equivalent is VBcrlf (\r\n means carriage return line feed)
|
|
|
|
|
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
|
|
|
|
|