|
the best way to approach this is to open excel and set the style to one of the cells in the document.
next, save the document as xml and open it using a text editor.
you should be able to see some style attribute applied to that cell. just mimic that style attribute in your own code.
cheers
|
|
|
|
|
Hi,
Does anybody know what versions of Excel support opening this xml styled xls?
Thanks,
Mark
_______________
0x2B || !0x2B
|
|
|
|
|
|
best way to see what you can do in excel is:
- open excel and create a cell exactly how you would want it
- save the excel document as an xml document
- open the file in notepad or some texteditor
- look for the style information for the cell you want
- you may be able to use DOM to dynamically load/append different styles to the same set of data, but i haven't done anything like that with this procedure
|
|
|
|
|
hi
the function written by u is excellent.
i just want how can add my own style to it or how can i give my own CSS to
this? i want to apply my CSS to this XML.
|
|
|
|
|
Excellent article. One quick thing I'm having a problem with though. The code runs fine and then I get a prompt to Open, Save, and Cancel. If I choose open and Excel is not currently running on my desktop I get the following error:
....{filename}[1].xls could not be found. Check the spelling of the file name, and verify that the file location is correct.
If Excel is running on my desk, the excel file displays correctly.
Any help would be greatly appreciated.
|
|
|
|
|
First of all, nice code, you've posted.
I've already used it in an C#-Application which worked perfect.
Now i'm transferring the code for an MFC-App which isn't a real problem, but i'm stuck with one problem:
How do i have to Format a Date/Time-String to let Excel realize the milliseconds in it?
I've tried "YY-MM-DD hh:mm:ss.ms" (e.g. "2006-11-21 17:45:13.32") but that doesn't work. Everything before the Milliseconds is realized correct.
Would really appreciate some help!
Thank, regards,
mike
|
|
|
|
|
the easiest way to see discover different formats is to construct an excel document by hand exactly like you want it.
- so create an excel document and add a cell that has the exact style you want (for example a specific date format).
- once you have manually entered the data, save the file as an xml spreadsheet and open it up in some text editor.
- you'll be able to deduce the styles on the cell.
- i was looking through the different date formats and i didn't see one that included milliseconds. you might want to split your data up into two separate columns (one column for the date, and the other for the time).
good luck
|
|
|
|
|
Thanks for your answer, but the way you did it in your sample-code ("YY-MM-DD hh:mm:ss.ms", e.g. "2006-11-21 17:45:13.32") is absolute correct.
The reason why it didn't worked the first time I've tried it, is, that i used the wrong format in Excel itself for the date-/time-cells (the export-format, which is mentioned above was ok).
The correct format to show milliseconds in an Excel-Cell is the following (only tried with an German version of Excel): "TT.MM.YY hh:mm:ss,00" (I'd guess that the English version would need this one "DD.MM.YY hh:mm:ss.00" but i don't know for sure).
Thankds, regards,
mike
|
|
|
|
|
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.
|
|
|
|
|