#region DataSet匯出Excel 文本方法支持大数据
protected void DsTotxt(DataSet ds)
{
string guid = Guid.NewGuid().ToString();
string path = Server.MapPath(string.Format("~/Files/Excel/{0}Excel.txt", guid));
string header =System.IO.File.ReadAllText(Server.MapPath("~/Files/Excel/header.txt"), Encoding.Default);//頭部文件
using (FileStream fs = new FileStream(path, FileMode.Create))
{
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(header);
foreach (System.Data.DataTable data in ds.Tables)
{
if (data != null && data.Rows.Count > 0)
{
int cloumnNum = 200;
//如果表格的列大于cloumnNum分割成多個表
if (data.Columns.Count > cloumnNum)
{
int column = data.Columns.Count / cloumnNum;
if ((data.Columns.Count % cloumnNum) > 0) column++;
for (int i = 0; i < column; i++)
{
string worksheetname = data.TableName.Replace("\r\n", "") + (i + 1);
sw.WriteLine(string.Format(@"<Worksheet ss:Name='{0}'>", worksheetname));
sw.WriteLine(@"<Table x:FullColumns='1' x:FullRows='1'>");
sw.WriteLine(@"\r\n<Row ss:AutoFitHeight='1'>");
int startcolumn = i * cloumnNum;//列开始
int endcolumn = (i + 1) * cloumnNum;//列结束
if (endcolumn > data.Columns.Count) endcolumn = data.Columns.Count;
for (int j = startcolumn; j < endcolumn; j++)
{
if (data.Columns.Count > j)
{
sw.WriteLine(string.Format(@"<Cell ss:StyleID='Header'><Data ss:Type='String'>{0}</Data></Cell>", data.Columns[j].ColumnName));
}
}
sw.WriteLine(@"\r\n</Row>");
// 輸出表格內容
foreach (System.Data.DataRow row in data.Rows)
{
sw.WriteLine("<Row>");
for (int b = startcolumn; b < endcolumn; b++)
{
// 对于数字,采用Number数字类型
string v = String.Format("{0}", row[data.Columns[b].ColumnName]);
string t = "String";
if (data.Columns[b].DataType == typeof(int))
{
t = "Number";
}
sw.WriteLine(string.Format("<Cell ss:StyleID='border'><Data ss:Type='{0}'>{1}</Data></Cell>", t, v));
}
sw.WriteLine("</Row>");
}
sw.WriteLine("</Table></Worksheet>");
}
}
else
{
sw.WriteLine(string.Format(@"<Worksheet ss:Name='{0}'>", data.TableName.Replace("\r\n", "")));
sw.WriteLine(@"<Table x:FullColumns='1' x:FullRows='1'>");
sw.WriteLine(@"\r\n<Row ss:AutoFitHeight='1'>");
cloumnNum = data.Columns.Count;
int startcolumn = 0;//列开始
int endcolumn = 1 * cloumnNum;//列结束
if (endcolumn > data.Columns.Count) endcolumn = data.Columns.Count;
for (int j = startcolumn; j < endcolumn; j++)
{
if (data.Columns.Count > j)
{
sw.WriteLine(string.Format(@"<Cell ss:StyleID='Header'><Data ss:Type='String'>{0}</Data></Cell>", data.Columns[j].ColumnName));
}
}
sw.WriteLine(@"\r\n</Row>");
// 輸出表格內容
foreach (System.Data.DataRow row in data.Rows)
{
sw.WriteLine("<Row>");
for (int b = startcolumn; b < endcolumn; b++)
{
// 对于数字,采用Number数字类型
string v = String.Format("{0}", row[data.Columns[b].ColumnName]);
string t = "String";
if (data.Columns[b].DataType == typeof(int))
{
t = "Number";
}
sw.WriteLine(string.Format("<Cell ss:StyleID='border'><Data ss:Type='{0}'>{1}</Data></Cell>", t, v));
}
sw.WriteLine("</Row>");
}
sw.WriteLine("</Table></Worksheet>");
}
}
}
sw.WriteLine("</Workbook>");
sw.Close();
}
string excelPath = Getpath();
System.IO.File.Move(path, Server.MapPath(excelPath));
Response.Redirect(excelPath, false);
Response.End();
}
//是否存在表名
private int nameCount = 1;
protected string ReName(DataSet ds, string name)
{
foreach (System.Data.DataTable item in ds.Tables)
{
if (item.TableName == name)
{
if (nameCount > 1)
{
name = name.Substring(0, name.Length - 1);
}
name = name + nameCount;
nameCount++;
name = ReName(ds, name);
}
}
return name;
}
//獲取路徑
private int fileCount = 0;
public string Getpath()
{
string _path = "~/Files/Excel/問卷訪問內容.xls";
if (fileCount != 0)
{
_path = string.Format("Files/Excel/問卷訪問內容{0}.xls", fileCount);
}
string FullPath = Server.MapPath(_path);
//存在刪除
if (System.IO.File.Exists(FullPath))
{
try
{
System.IO.File.Delete(FullPath);
}
catch (Exception e)
{
fileCount++;
return Getpath();
}
}
return _path;
}
#endregion
以下是header.txt內容
---------------------------------------------------------------------------------------------------------------------------------------------
<?xml version='1.0'?>
<?mso-application progid='Excel.Sheet'?>
<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>
<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
<Author>limin_he</Author><LastAuthor>limin_he</LastAuthor><Created><%=DateTime.Now.ToString() %></Created><Company>Maxense</Company><Version>2013</Version>
</DocumentProperties>
<Styles>
<Style ss:ID='Default' ss:Name='Normal'>
<Alignment ss:Vertical='Center'/>
<Borders/>
<Font ss:FontName='新細明體' x:CharSet='134' ss:Size='12'/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID='Header'>
<Alignment ss:Horizontal="Center" ss:Vertical="Center" />
<Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
</Borders>
<Font ss:FontName='新細明體' x:CharSet='134' ss:Size='12' ss:Color='#000000'/>
</Style>
<Style ss:ID='border'>
<NumberFormat ss:Format='@'/>
<Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
</Borders>
</Style>
</Styles>