结果导出成Excel文件. 用几个不同的办法做了出来. 最后部署时发现: 用COM+组件行不通, 服务器上没装OFFICE; 用OWC也不行, 因为服务器是64位的, OWC不支持; 导出成Excel2003支持的xml, 这招最灵活, 也是一个简单可行的办法.
首先用Excel 2003新建一个空白Wookbook, 然后保存为XML Spreadsheet. 然后打开看它生成的XML代码就能了解XML Spreadsheet的基本结构了.
我先把DataTable生成XML数据, 再使用一个XLS把XML数据转换成XML Spreadsheet的结构.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public class DataSetToExcel
{
/// <summary>
/// 生成EXCEL
/// </summary>
public DataSetToExcel()
{ }
/// <summary>创建Excel的xml文件
///
/// </summary>
/// <param name="DS">数据</param>
/// <param name="fileName">文件名</param>
public DataSetToExcel(DataSet DS, string fileName)
{
CreateXmlExcelFile(DS, fileName);
}
/// <summary> 创建Excel的xml文件
///
/// </summary>
/// <param name="DS">数据</param>
/// <param name="fileName">文件名</param>
public void CreateXmlExcelFile(DataSet DS, string fileName)
{
string foldName = fileName;
#region 同名文件删除
FileInfo FI = new FileInfo(foldName);
if (FI.Exists)
{
FI.Delete();
}
#endregion
#region 创建XML对象,二维数组写入并保存
//创建一个XML文档
XmlDocument xmldoc = new XmlDocument();
XmlNode xmldc = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
xmldoc.AppendChild(xmldc);
XmlProcessingInstruction xmlpi = xmldoc.CreateProcessingInstruction("mso-application", "progid=/"Excel.Sheet/"");
xmldoc.AppendChild(xmlpi);
//创建一个工作簿
XmlElement xmlwb = xmldoc.CreateElement("Workbook");
xmlwb.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
xmlwb.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
xmlwb.SetAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
xmlwb.SetAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
xmlwb.SetAttribute("xmlns:html", "http://www.w3.org/TR/REC-html40");
xmldoc.AppendChild(xmlwb);
XmlElement xmlsts = xmldoc.CreateElement("Styles");
xmlwb.AppendChild(xmlsts);
XmlElement xmlst = xmldoc.CreateElement("Style");
XmlAttribute xmlarr = xmldoc.CreateAttribute("ss:ID", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "s21";
xmlst.Attributes.Append(xmlarr);
xmlsts.AppendChild(xmlst);
XmlElement xmlnf = xmldoc.CreateElement("NumberFormat");
xmlarr = xmldoc.CreateAttribute("ss:Format", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "Short Date";
xmlnf.Attributes.Append(xmlarr);
xmlst.AppendChild(xmlnf);
foreach (DataTable DT in DS.Tables)
{
XmlElement xmlws = xmldoc.CreateElement("Worksheet");
xmlarr = xmldoc.CreateAttribute("ss:Name", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = DT.TableName.Replace("/", "");
xmlws.Attributes.Append(xmlarr);
xmlwb.AppendChild(xmlws);
XmlElement xmltb = xmldoc.CreateElement("Table");
xmlws.AppendChild(xmltb);
XmlElement xmlrw = xmldoc.CreateElement("Row");
xmltb.AppendChild(xmlrw);
foreach (DataColumn DC in DT.Columns)
{
XmlElement xmlcl = xmldoc.CreateElement("Cell");
xmlrw.AppendChild(xmlcl);
XmlElement xmldt = xmldoc.CreateElement("Data");
xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "String";
xmldt.Attributes.Append(xmlarr);
XmlText xmltxt = xmldoc.CreateTextNode(DC.ColumnName);
xmldt.AppendChild(xmltxt);
xmlcl.AppendChild(xmldt);
}
foreach (DataRow DR in DT.Rows)
{
xmlrw = xmldoc.CreateElement("Row");
xmltb.AppendChild(xmlrw);
foreach (DataColumn DC in DT.Columns)
{
XmlElement xmlcl = xmldoc.CreateElement("Cell");
XmlElement xmldt = xmldoc.CreateElement("Data");
XmlText xmltxt;
switch (DC.DataType.Name.ToLower())
{
case "int32":
case "decimal":
case "double":
xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "Number";
xmldt.Attributes.Append(xmlarr);
xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
break;
case "datetime":
case "smalldatetime":
xmlarr = xmldoc.CreateAttribute("ss:StyleID", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "s21";
xmlcl.Attributes.Append(xmlarr);
xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "DateTime";
xmldt.Attributes.Append(xmlarr);
xmltxt = xmldoc.CreateTextNode(Convert.ToDateTime(DR[DC.ColumnName]).ToString("yyyy-MM-ddTHH:mm:ss").Trim());
break;
default:
xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
xmlarr.Value = "String";
xmldt.Attributes.Append(xmlarr);
xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
break;
}
xmldt.AppendChild(xmltxt);
xmlcl.AppendChild(xmldt);
xmlrw.AppendChild(xmlcl);
}
}
}
xmldoc.Save(foldName);
System.Diagnostics.Process.Start(foldName);
#endregion
}
}