-
导出导入Excel(DataSet,DataGrid)
public class ExportExcel
{
public ExportExcel()
{
}
private static void GetXslFile(DataSet ds,string xslPath)
{
string strColumn = "";
string strRow = "";
string dsName = ds.DataSetName;
string tableName = ds.Tables[0].TableName;
string header = dsName + "/" + tableName;
foreach(DataColumn clm in ds.Tables[0].Columns)
{
//特殊字符 <,>,",*,%,(,),& 替换
//*************************************************
//*************************************************
// 符号 xml下的值 excel中的值
// < -------- _x003C_ ------ <
// > -------- _x003E_ ------ >
// " -------- _x0022_ ------ "
// * -------- _x002A_ ------ *
// % -------- _x0025_ ------ %
// & -------- _x0026_ ------ &
// ( -------- _x0028_ ------ (
// ) -------- _x0029_ ------ )
// = -------- _x003D_ ------ =
//*************************************************
//*************************************************
string strClmName = clm.ColumnName;
string strRowName = clm.ColumnName;
if(strClmName.IndexOf("&")!=-1)
{
strClmName = strClmName.Replace("&","&");
}
if(strClmName.IndexOf("<")!=-1)
{
strClmName = strClmName.Replace("<","<");
}
if(strClmName.IndexOf(">")!=-1)
{
strClmName=strClmName.Replace(">",">");
}
if(strClmName.IndexOf("\"")!=-1)
{
strClmName=strClmName.Replace("\"",""");
}
if(strRowName.IndexOf("<")!=-1)
{
strRowName=strRowName.Replace("<","_x003C_");
}
if(strRowName.IndexOf(">")!=-1)
{
strRowName=strRowName.Replace(">","_x003E_");
}
if(strRowName.IndexOf("\"")!=-1)
{
strRowName=strRowName.Replace("\"","_x0022_");
}
if(strRowName.IndexOf("*")!=-1)
{
strRowName=strRowName.Replace("*","_x002A_");
}
if(strRowName.IndexOf("%")!=-1)
{
strRowName=strRowName.Replace("%","_x0025_");
}
if(strRowName.IndexOf("&")!=-1)
{
strRowName=strRowName.Replace("&","_x0026_");
}
if(strRowName.IndexOf("(")!=-1)
{
strRowName=strRowName.Replace("(","_x0028_");
}
if(strRowName.IndexOf(")")!=-1)
{
strRowName=strRowName.Replace(")","_x0029_");
}
if(strRowName.IndexOf("=")!=-1)
{
strRowName=strRowName.Replace("=","_x003D_");
}
strColumn += "<th>" + strClmName +"</th>" + "\r\n";
strRow += "<td>" + "<xsl:value-of select=" + "\"" + strRowName + "\"" +"/>" + "</td>" + "\r\n";
}
string str = @"<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">
<xsl:template match=""/"">
<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40"">
<head>
<meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" />
<style>
.xl24{mso-style-parent:style0;mso-number-format:""\@"";text-align:right;}
</style>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
<body> ";
str += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0""><tr>" + "\r\n";
str += strColumn;
str += @" </tr><xsl:for-each select="""+header+@"""><tr>";
str += "\r\n" + strRow;
str += @"</tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet> ";
string path =xslPath;
if(File.Exists(path))
{
File.Delete(path);
}
FileStream fs = File.Create(path);
StreamWriter sw=new StreamWriter(fs);
sw.Write(str);
sw.Close();
fs.Close();
}
private static void GetXmlFile(DataSet ds,string xmlFilePath)
{
string strXml = ds.GetXml();
if(File.Exists(xmlFilePath))
{
File.Delete(xmlFilePath);
}
FileStream fs1 = File.Create(xmlFilePath);
StreamWriter writer = new StreamWriter(fs1);
writer.Write(strXml);
writer.Close();
fs1.Close();
}
private static void BuildExcel(DataSet ds,string path)
{
if(File.Exists(path))
{
File.Delete(path);
}
string m_path = path.Substring(0,path.Length-4);
string m_fileXml = m_path + ".xml";
string m_fileXsl = m_path + ".xsl";
string m_fileXls = m_path + ".xls";
try
{
GetXmlFile(ds,m_fileXml);
GetXslFile(ds,m_fileXsl);
//Excel changed
XmlDocument doc = new XmlDocument();
doc.Load(m_fileXml);
XslTransform xslt = new XslTransform();
xslt.Load(m_fileXsl);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
XmlTextWriter writer = new XmlTextWriter(m_fileXls,null);
xslt.Transform(nav,null,writer,null);
writer.Close();
File.Delete(m_fileXml);
File.Delete(m_fileXsl);
}
catch
{
throw;
}
}
public static void ToExcel(System.Web.UI.Control ctl,string FileName)
{
HttpContext.Current.Response.Charset ="UTF-8";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType ="application/ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
public static void DownloadFile(string physicalFilePath)
{
FileStream stream=null;
try
{
stream = new FileStream(physicalFilePath, FileMode.Open, FileAccess.Read, FileShare.Read);
int bufSize = (int)stream.Length;
byte[] buf = new byte[bufSize];
int bytesRead = stream.Read(buf, 0, bufSize);
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+System.IO.Path.GetFileName(physicalFilePath));
HttpContext.Current.Response.OutputStream.Write(buf, 0, bytesRead);
HttpContext.Current.Response.End();
}
finally
{
stream.Close();
}
}
}
-
相关阅读:
298. Binary Tree Longest Consecutive Sequence
117. Populating Next Right Pointers in Each Node II
116. Populating Next Right Pointers in Each Node
163. Missing Ranges
336. Palindrome Pairs
727. Minimum Window Subsequence
211. Add and Search Word
年底购物狂欢，移动支付安全不容忽视
成为程序员前需要做的10件事
全球首推iOS应用防破解技术！
-
原文地址：https://www.cnblogs.com/studio313/p/298958.html
Copyright © 2020-2023
润新知