|
At the beginning of the function I write out style formats for the excel document. you'll want to change the following style tag to your preferred time format (not sure what the format can be):
<Style ss:ID="DateLiteral">
<NumberFormat ss:Format="mm/dd/yyyy;@"/>
</Style>
|
|
|
|
|
Haii ALl,
Can any one pla tell me how to add multiple sheets in excell using c#
|
|
|
|
|
Study the code. Tell me what you've tried so far.
|
|
|
|
|
Can u tell me how to add header ,datetime and footer in this i able to export the data in to different worksheet but i want to add header in each worksheet and datetime when user print could any tell me how i can do that, i will be appreciated, thanks for the reply
|
|
|
|
|
Good article! I made a variation on the vb.net version of the previous poster, so it does no longer support styles, which makes it simpler and more general IMHO. I turned it into a class and compiled it as dll. I also extended it with the possibility to include a sheet name, where the sheetname becomes something like "Sheetn_xxxxxx" where xxxxx represents the name you give as parameter and n the sheet order number.
Here is the call:
Dim myStream As New System.IO.StringWriter()
Dim dstox As New DatasetToExcel.DatasetToExcel
myStream = dstox.exportToExcel(dsTotalSalesJanuary, "Jan2009")
myStream.Close()
And here is the code:
Imports System.Text
Public Class DatasetToExcel
Public Function exportToExcel(ByVal source As DataSet, ByVal sheetName As String)
Dim excelDoc As New System.IO.StringWriter()
Dim sb As New StringBuilder
sb.Append("<?xml version=""1.0""?>")
sb.Append(vbCrLf)
sb.Append("<?mso-application progid=""Excel.Sheet""?>")
sb.Append(vbCrLf)
sb.Append("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")
sb.Append(vbCrLf)
sb.Append("xmlns =""urn:schemas-microsoft-com:office:office""")
sb.Append(vbCrLf)
sb.Append("xmlns:x=""urn:schemas-microsoft-com:office:excel""")
sb.Append(vbCrLf)
sb.Append("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
sb.Append(vbCrLf)
sb.Append("xmlns:html=""http://www.w3.org/TR/REC-html40"">")
sb.Append(vbCrLf)
Dim startExcelXML As String = sb.ToString
Const endExcelXML As String = "</Workbook>"
Dim rowCount As Integer = 0
Dim sheetCount As Integer = 1
'WORKSHEET AND TABLE TAGS:
excelDoc.Write(startExcelXML)
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & "_" & sheetName & """>")
excelDoc.Write("<Table>")
'CREATE HEADER ROW:
excelDoc.Write("<Row>")
For x As Integer = 0 To source.Tables(0).Columns.Count - 1
excelDoc.Write("<Cell><Data ss:Type=""String"">")
excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
excelDoc.Write("</Data></Cell>")
Next
excelDoc.Write("</Row>")
'INSERT THE DATA ROWS:
For Each x As DataRow In source.Tables(0).Rows
rowCount += 1
If rowCount = 64000 Then
rowCount = 0
sheetCount += 1
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
End If
excelDoc.Write("<Row>")
For y As Integer = 0 To source.Tables(0).Columns.Count - 1
Dim rowType As System.Type
rowType = x(y).GetType()
Select Case rowType.ToString()
Case "System.String"
Dim XMLstring As String = x(y).ToString()
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("<", "<")
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write(XMLstring)
excelDoc.Write("</Data></Cell>")
Exit Select
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
'The Following Code puts the date stored in XMLDate
'to the format above
Dim XMLDate As DateTime = DirectCast(x(y), DateTime)
Dim XMLDatetoString As String = ""
'Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() & "-" & (If(XMLDate.Month < 10, "0" & XMLDate.Month.ToString(), XMLDate.Month.ToString())) & "-" & (If(XMLDate.Day < 10, "0" & XMLDate.Day.ToString(), XMLDate.Day.ToString())) & "T" & (If(XMLDate.Hour < 10, "0" & XMLDate.Hour.ToString(), XMLDate.Hour.ToString())) & ":" & (If(XMLDate.Minute < 10, "0" & XMLDate.Minute.ToString(), XMLDate.Minute.ToString())) & ":" & (If(XMLDate.Second < 10, "0" & XMLDate.Second.ToString(), XMLDate.Second.ToString())) & ".000"
excelDoc.Write("<Cell ss:StyleID=""DateLiteral"">" & "<Data ss:Type=""DateTime"">")
'excelDoc.Write("<Cell>" & "<Data ss:Type=""DateTime"">")
excelDoc.Write(XMLDatetoString)
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Boolean"
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
excelDoc.Write("<Cell>" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Decimal", "System.Double"
excelDoc.Write("<Cell>" & "<Data ss:Type=""Number"">")
excelDoc.Write(Replace(x(y).ToString(), ",", "."))
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.DBNull"
excelDoc.Write("<Cell>" & "<Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next
excelDoc.Write("</Row>")
Next
'INSERT THE CLOSING TAGS:
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write(endExcelXML)
excelDoc.Close()
'RETURN THE DOCUMENT AS STRINGWRITER
Return excelDoc
End Function
End Class
Thank you Xodiak for an excellent article!
|
|
|
|
|
I took what you had converted it to VB.NET and then changed from StreamWriter to StringWriter. Now it asks if you want to save the Excel doc. Thaought someone else may be interested to see it. The code you wrote works great. Thanks so much for posting it.
<pre>
Protected Sub exportToExcel(ByVal source As DataSet)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim excelDoc As New System.IO.StringWriter()
'Dim excelDoc As System.IO.StreamWriter
'excelDoc = New System.IO.StreamWriter(fileName)
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>"
Dim rowCount As Integer = 0
Dim sheetCount As Integer = 1
'
' <xml version>
' <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
' xmlns ="urn:schemas-microsoft-com:office:office"
' xmlns:x="urn:schemas-microsoft-com:office:excel"
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
' <Styles>
' <Style ss:ID="Default" ss:Name="Normal">
' <Alignment ss:Vertical="Bottom"/>
' <Borders/>
' <Font/>
' <Interior/>
' <NumberFormat/>
' <Protection/>
' </Style>
' <Style ss:ID="BoldColumn">
' <Font x:Family="Swiss" ss:Bold="1"/>
' </Style>
' <Style ss:ID="StringLiteral">
' <NumberFormat ss:Format="@"/>
' </Style>
' <Style ss:ID="Decimal">
' <NumberFormat ss:Format="0.0000"/>
' </Style>
' <Style ss:ID="Integer">
' <NumberFormat ss:Format="0"/>
' </Style>
' <Style ss:ID="DateLiteral">
' <NumberFormat ss:Format="mm/dd/yyyy;@"/>
' </Style>
' </Styles>
' <Worksheet ss:Name="Sheet1">
' </Worksheet>
' </Workbook>
'
excelDoc.Write(startExcelXML)
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
excelDoc.Write("<Row>")
For x As Integer = 0 To source.Tables(0).Columns.Count - 1
excelDoc.Write("<Cell ss:StyleID=""BoldColumn""><Data ss:Type=""String"">")
excelDoc.Write(source.Tables(0).Columns(x).ColumnName)
excelDoc.Write("</Data></Cell>")
Next
excelDoc.Write("</Row>")
For Each x As DataRow In source.Tables(0).Rows
rowCount += 1
'if the number of rows is > 64000 create a new page to continue output
If rowCount = 64000 Then
rowCount = 0
sheetCount += 1
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write("<Worksheet ss:Name=""Sheet" & sheetCount & """>")
excelDoc.Write("<Table>")
End If
excelDoc.Write("<Row>")
'ID=" + rowCount + "
For y As Integer = 0 To source.Tables(0).Columns.Count - 1
Dim rowType As System.Type
rowType = x(y).[GetType]()
Select Case rowType.ToString()
Case "System.String"
Dim XMLstring As String = x(y).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>")
Exit Select
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
'The Following Code puts the date stored in XMLDate
'to the format above
Dim XMLDate As DateTime = DirectCast(x(y), DateTime)
Dim XMLDatetoString As String = ""
'Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() & "-" & (If(XMLDate.Month < 10, "0" & XMLDate.Month.ToString(), XMLDate.Month.ToString())) & "-" & (If(XMLDate.Day < 10, "0" & XMLDate.Day.ToString(), XMLDate.Day.ToString())) & "T" & (If(XMLDate.Hour < 10, "0" & XMLDate.Hour.ToString(), XMLDate.Hour.ToString())) & ":" & (If(XMLDate.Minute < 10, "0" & XMLDate.Minute.ToString(), XMLDate.Minute.ToString())) & ":" & (If(XMLDate.Second < 10, "0" & XMLDate.Second.ToString(), XMLDate.Second.ToString())) & ".000"
excelDoc.Write("<Cell ss:StyleID=""DateLiteral"">" & "<Data ss:Type=""DateTime"">")
excelDoc.Write(XMLDatetoString)
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Boolean"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Int16", "System.Int32", "System.Int64", "System.Byte"
excelDoc.Write("<Cell ss:StyleID=""Integer"">" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.Decimal", "System.Double"
excelDoc.Write("<Cell ss:StyleID=""Decimal"">" & "<Data ss:Type=""Number"">")
excelDoc.Write(x(y).ToString())
excelDoc.Write("</Data></Cell>")
Exit Select
Case "System.DBNull"
excelDoc.Write("<Cell ss:StyleID=""StringLiteral"">" & "<Data ss:Type=""String"">")
excelDoc.Write("")
excelDoc.Write("</Data></Cell>")
Exit Select
Case Else
Throw (New Exception(rowType.ToString() & " not handled."))
End Select
Next
excelDoc.Write("</Row>")
Next
excelDoc.Write("</Table>")
excelDoc.Write(" </Worksheet>")
excelDoc.Write(endExcelXML)
Response.Write(excelDoc)
excelDoc.Close()
End Sub
</pre>
|
|
|
|
|
Hello, do you think write this example in vb.net ?
Fernando.
|
|
|
|
|
Here is the code if your dataset contains multiple datatables. Pretty simple change.
public static void exportToExcel(DataSet source, string fileName)
{
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "<xml version="">\r\n<workbook hold=" /> " xmlns="\" urn:schemas-microsoft-com:office:spreadsheet\"\r\n""="" mode="hold"> " xmlns =\"urn:schemas-microsoft-com:office:office\"\r\n " +
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n <styles>\r\n " +
"\r\n " +
"<alignment ss:vertical="\"Bottom\"/">\r\n <borders />" +
"\r\n <font />\r\n <interior />\r\n <numberformat />" +
"\r\n <protection />\r\n </alignment>\r\n " +
"\r\n <font hold=" /> " x:family="\"Swiss\"" ss:bold="\"1\"/" xmlns:x="#unknown">\r\n </font>\r\n " +
"\r\n <numberformat"> " ss:Format=\"@\"/>\r\n \r\n \r\n <numberformat hold=" /> " ss:format="\"0.0000\"/">\r\n </numberformat>\r\n " +
"\r\n <numberformat hold=" /> " ss:format="\"0\"/">\r\n </numberformat>\r\n \r\n <numberformat hold=" /> " ss:format="\"mm/dd/yyyy;@\"/">\r\n </numberformat>\r\n " +
"\r\n ";
const string endExcelXML = "";
int rowCount = 0;
int sheetCount = 0;
excelDoc.Write(startExcelXML);
foreach (DataTable dt in source.Tables)
{
sheetCount++;
excelDoc.Write("<worksheet ss:name="\" sheet""="" sheetcount="" xmlns:ss="#unknown">");
excelDoc.Write("");
excelDoc.Write("<row>");
for (int x = 0; x < dt.Columns.Count; x++)
{
excelDoc.Write("<cell ss:styleid="\" boldcolumn\""="">");
excelDoc.Write(dt.Columns[x].ColumnName);
excelDoc.Write("");
}
excelDoc.Write("");
foreach (DataRow x in dt.Rows)
{
rowCount++;
//if the number of rows is > 64000 create a new page to continue output
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("");
excelDoc.Write(" ");
excelDoc.Write("<worksheet ss:name="\" sheet""="" sheetcount="" xmlns:ss="#unknown">");
excelDoc.Write("");
}
excelDoc.Write("<row>"); //ID=" + rowCount + "
for (int y = 0; y < dt.Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch (rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("<", "<");
excelDoc.Write("<cell ss:styleid="\" stringliteral\""="">" +
"");
excelDoc.Write(XMLstring);
excelDoc.Write("");
break;
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
//The Following Code puts the date stored in XMLDate
//to the format above
DateTime XMLDate = (DateTime)x[y];
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month < 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
excelDoc.Write("<cell ss:styleid="\" dateliteral\""="">" +
"");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("");
break;
case "System.Boolean":
excelDoc.Write("<cell ss:styleid="\" stringliteral\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<cell ss:styleid="\" integer\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<cell ss:styleid="\" decimal\""="">" +
"");
excelDoc.Write(x[y].ToString());
excelDoc.Write("");
break;
case "System.DBNull":
excelDoc.Write("<cell ss:styleid="\" stringliteral\""="">" +
"");
excelDoc.Write("");
excelDoc.Write("");
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("");
}
excelDoc.Write("");
excelDoc.Write(" ");
}
excelDoc.Write(endExcelXML);
excelDoc.Close();
}
|
|
|
|
|
first : thanks for useful method.
i have a datagridview in a from and i want to export the data of this datagridview to excel file ,
in this method,the input argument is DATASET and i want to change datagridbiew content to dataset to using ur method,how can i do that?
best regards.
|
|
|
|
|
what is the data grid view bound to?
|
|
|
|
|
Thanks for the code. It solves a lot of issues. I have one problem but it thinks it's an excel problem. When I try and open the files, after running the code, I get errors due to some of the year values being less than 1900.
Any ideas?
Thanks!
jeff
|
|
|
|
|
I have a temporary fix for this issue. If the date is less than 1900, I treat it as a string.
|
|
|
|
|
Do you mean that you face a problem when you put a year less then 1900, or you are just inserting time and you see 1900 is calculated with it automatically.
can you please give more details so we can help you about it.
|
|
|
|
|
Friends,
I'm having problem to export a DataSet using XML, because the client's Office is Version 2000.
When the file .xls is opened, only is show some tags of XML and don't show datas of DataSet.
How can I solve this problem?
Hugs,
Reginaldo Pereira
|
|
|
|
|
Helow,
Please help me?
I don't know how to I solve this?
Reginaldo
|
|
|
|
|
|
By the way XML format in excel is supported in
MS office 2000 SP3
MS Office XP SP3
MS office 2003
MS office 2007
|
|
|
|
|
hellow,
I would like to fill with color a cell. How to I do this? I tried "<interior ss:color="#000000" ,="" but="" i="" don="t need.">
Someone Can I help me? please
Hugs,
Reginaldo Pereira
|
|
|
|
|
|
|
Use this method, we also can export gridview data which datasource is dataset to excel and save it in client side
-- modified at 1:38 Thursday 29th November, 2007
Better Coder, Better Life
|
|
|
|
|
Hi,
Is it possible to write the dataset into an existing xls on the system. Currently the method creates a new xls and writes the data there.
I've existing excek templates and would simply like to load the data in it.
Btw your code is really helpful!
Girish
|
|
|
|
|
This method has one serious drawback. If you try to use Ole Db to read data from the generated excel file you will get an exception because the file in reality is xml file, not native excel xsl file.
|
|
|
|
|
you're right. i think some earlier post shows how to do the export to excel using an oledb provider. if i need a native xsl file, i just open the exported xml and save as xsl.
|
|
|
|
|
Xodiak wrote: if i need a native xsl file, i just open the exported xml and save as xsl.
That can be done programmatically to like this:
<br />
private void savetoexcelformat(string currentpath, string pathtosave)<br />
{<br />
object missing = System.Reflection.Missing.Value;<br />
<br />
Excel.Application app = new Excel.Application();<br />
app.Visible = false;<br />
<br />
app.Workbooks.OpenXML(currentpath, missing,missing).SaveAs(pathtosave, Excel.XlFileFormat.xlWorkbookNormal, missing, missing,<br />
missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);<br />
app.Quit();<br />
<br />
}
|
|
|
|
|