导出数据到execl如何设置sheet的名称

[xiaoxian1314]

我用下面这个函数来把数据导出到execl
public void CreateExcel(DataSet ds)
{

HttpResponse resp;

resp = Page.Response;

resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

resp.ContentType = "application/ms-excel";

resp.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

string colHeaders = "", ls_item = "";

int i = 0;

//定义表对象与行对像，同时用DataSet对其值进行初始化

DataTable dt = ds.Tables[0];

DataRow[] myRow = dt.Select("");

//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符

for (i = 0; i < ds.Tables[0].Columns.Count; i++)

colHeaders += dt.Columns[i].Caption.ToString() + "\t";

colHeaders += "\n";

//向HTTP输出流中写入取得的数据信息

resp.Write(colHeaders);

//逐行处理数据

foreach (DataRow row in myRow)
{

//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n

for (i = 0; i < ds.Tables[0].Columns.Count; i++)

ls_item += row[i].ToString() + "\t";

ls_item += "\n";

//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据

resp.Write(ls_item);

ls_item = "";

}

resp.End();

}
导出后的电子表格的sheet的名称与文件名一样，但是我想统一设置成Sheet1，请高手指点一下！多谢了！

[mfijing]

建议采用ADO．NET 的方式读取Excel,把Excel当个数据库，每个sheet就是一张表，sheet名就是表名！

[ckpckphaha]

心有余而力不足，帮楼主顶顶

[jiaoding]

只能顶了

[purplesunshine]

1 建立一个Excel.Workbook ，例如workBook_obj
2 Excel.Worksheet Sheet_obj = (Excel.Worksheet)workBook_obj.Sheets[1];

具体实例：http://blog.csdn.net/jamex/archive/2006/03/29/643129.aspx

[purplesunshine]

建立了Sheet_obj就可以操作其具体的Name项了。

[wizardlun]

private void DSToExcel(DataTable dt) {
string strFile = "";
string path = "";
strFile = strFile + "DateQueryReport_";
strFile = strFile + DateTime.Now.ToString("yyyyMMddHHmm");
strFile = strFile + ".xls";
string fileName = strFile;
path = Server.MapPath(strFile);
System.IO.FileStream fs = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, new System.Text.UnicodeEncoding());
sw.Write("申請時間");
sw.Write("\t");
sw.Write("申請單位");
sw.Write("\t");
sw.Write("申請人");
sw.Write("\t");
sw.Write("日期從");
sw.Write("\t");
sw.Write("日期止");
sw.Write("\t");
sw.Write("車號");
sw.Write("\t");
sw.Write("駕駛員");
sw.Write("\t");
sw.WriteLine("");

for (int i = 0; i < dt.Rows.Count;i++ )
{
sw.Write(dt.Rows[i]["申請時間"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["申請單位"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["申請人"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["日期從"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["日期止"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["車號"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["駕駛員"].ToString());
sw.Write("\t");
sw.WriteLine("");
}
sw.Flush();
sw.Close();
try
{
if(path!="")
{
DownFile(path,fileName);
}
}
catch(Exception e)
{
//Response.Write(e);
throw(e);
//Response.Write("<script language='javascript'>alert('error')</script>");
Response.Write("failed");
}
}

[purplesunshine]

Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oCells;
string sFile = Server.MapPath("\\MyExcel.xls");
oExcel.Visible = false;
oExcel.DisplayAlerts = false ;
oBooks = oExcel.Workbooks;
oBooks.Add(Type.Missing);
oBook=oExcel.ActiveWorkbook;
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets[1];
oSheet.Name = "Second Sheet";
oCells = oSheet.Cells; 　
oCells[2,5] = "测试数据A";
oCells[2,6] = "测试数据B";
//这里要根据你机器上office的版本调整参数个数，自己看一下saveas方法需要几个参数，适当添加或删除，我现在用的2007，10参数。
oSheet.SaveAs(sFile ,Type.Missing,Type.Missing,Type.Missing,Type.Missing ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
oBook.Close(null,null,null);
oExcel.Quit();
Marshal.ReleaseComObject(oCells);
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oSheets);
Marshal.ReleaseComObject(oBook) ;
Marshal.ReleaseComObject(oBooks) ;
Marshal.ReleaseComObject(oExcel);
oExcel = null ;
oBooks = null ;
oBook = null;
oSheets = null ;
oSheet = null ;
oCells = null;
System.GC.Collect();
Response.Write("<script>alert('生成Excel成功！请在路径'+'"+ str +"'+'下查询文件');</script>");

[codeangel]

DataGrid内容导出标准的Excel格式文件
作者：Super


前言：

用传统的导出方法：只是将DataGrid信息用html输出，文件名后辍是.xls而已。如果想将这个方法导入到Sql Server 中，会提示出错。因为它不是标准的Excel格式文件。

用本例中的导出方法：会输出标准的Excel格式文件，非常稳定，不会死锁Excel进程，支持中文文件名，支持表头导出，支持大多数数据库导入。


实现算法：

利用Excel组件将DataGrid控件内容生成Excel临时文件，并存放在服务器上，并用Response方法将生成的Excel文件下载到客户端，再将生成的临时文件删除。

具体步骤：

1.在项目中引用Excel组件
Interop.Excel.dll 文件版本1.3.0.0


2.项目中应有一个目录（本例中Template目录），以便存放Excel文件（名字自己定）


3.导入方法类

以下是代码片段：

/// <summary>
/// 将DataGrid中数据导出至Excel，生成标准的Excel文件
/// </summary>
/// <param name="grid">DataGrid控件ID</param>
/// <param name="fileName">导出文件名</param>
protected void ExportToExcel(System.Web.UI.WebControls.DataGrid grid,string fileName)
{
string templetFilePath;
templetFilePath = Server.MapPath("../").ToString() + @"Template\";
object missing = Missing.Value;
Excel.Application app;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
Excel.Range range;


//创建一个Application对象并使其不可见
app = new Excel.ApplicationClass();
app.Visible=false;


//打开模板文件，得到WorkBook对象
//workBook = app.Workbooks.Open(templetFilePath + "SuperTemplet.xls", missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing);

//创建一个WorkBook对象
workBook = app.Workbooks.Add(missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

int rowCount = grid.Items.Count + 1; //DataTable行数+GirdHead
int colCount = grid.Columns.Count; //DataTable列数

//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];

for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
if (j == 0)
{
arr[j, k] = grid.Columns[k].HeaderText;

}
else
{
arr[j, k] = grid.Items[j - 1].Cells[k].Text.ToString();
}
}
}

range = (Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.Value = arr;

workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing,Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);

if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}

if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}

if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}

GC.Collect();//强制代码垃圾回收

//下载文件
DownLoadFile(templetFilePath,fileName);
}




4.下载文件方法类
以下是代码片段：

/// <summary>
/// 下载服务器文件到客户端
/// </summary>
/// <param name="_FilePath">文件路径</param>
/// <param name="_FileName">文件名</param>
/// <returns>返回 bool型</returns>
private bool DownLoadFile(string _FilePath,string _FileName)
{
try
{
System.IO.FileStream fs = System.IO.File.OpenRead(_FilePath+_FileName);
byte[] FileData = new byte[fs.Length];
fs.Read(FileData, 0, (int)fs.Length);
Response.Clear();
Response.AddHeader("Content-Type", "application/ms-excel");
string FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_FileName));
Response.AddHeader("Content-Disposition", "inline;filename=" + System.Convert.ToChar(34) + FileName + System.Convert.ToChar(34));
Response.AddHeader("Content-Length", fs.Length.ToString());
Response.BinaryWrite(FileData);
fs.Close();
//删除服务器临时文件
System.IO.File.Delete(_FilePath+_FileName);
Response.Flush();
Response.End();

return true;
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
}


5.应用方法
以下是代码片段：
protected void btnExportToExcel_Click(object sender, EventArgs e)
{

this.ExportToExcel(grdBudget,"中国石油大港油田油管厂发料记录.xls");//grdBudget 是DataGrid的ID

}

6.有问题请联系
QQ：695395 主页：http://www.wsoft.net

[cpp2017]

如果用html导出就要用这种格式来设置 <!--[if gte mso 9]><xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:Name>Sheet1</x:Name> <x:WorksheetOptions> <x:DefaultRowHeight>240</x:DefaultRowHeight> <x:Print> <x:ValidPrinterInfo/> <x:PaperSizeIndex>9</x:PaperSizeIndex> <x:HorizontalResolution>300</x:HorizontalResolution> <x:VerticalResolution>300</x:VerticalResolution> </x:Print> <x:Selected/> <x:TopRowVisible>9</x:TopRowVisible> <x:LeftColumnVisible>3</x:LeftColumnVisible> <x:Panes> <x:Pane> <x:Number>3</x:Number> <x:ActiveRow>1</x:ActiveRow> <x:ActiveCol>5</x:ActiveCol> </x:Pane> </x:Panes> <x:ProtectContents>False</x:ProtectContents> <x:ProtectObjects>False</x:ProtectObjects> <x:ProtectScenarios>False</x:ProtectScenarios> </x:WorksheetOptions> </x:ExcelWorksheet> <x:ExcelWorksheet> <x:Name>Sheet2</x:Name> <x:WorksheetOptions> <x:DefaultRowHeight>285</x:DefaultRowHeight> <x:ProtectContents>False</x:ProtectContents> <x:ProtectObjects>False</x:ProtectObjects> <x:ProtectScenarios>False</x:ProtectScenarios> </x:WorksheetOptions> </x:ExcelWorksheet> <x:ExcelWorksheet> <x:Name>Sheet3</x:Name> <x:WorksheetOptions> <x:DefaultRowHeight>285</x:DefaultRowHeight> <x:ProtectContents>False</x:ProtectContents> <x:ProtectObjects>False</x:ProtectObjects> <x:ProtectScenarios>False</x:ProtectScenarios> </x:WorksheetOptions> </x:ExcelWorksheet> </x:ExcelWorksheets> <x:WindowHeight>8445</x:WindowHeight> <x:WindowWidth>14955</x:WindowWidth> <x:WindowTopX>120</x:WindowTopX> <x:WindowTopY>45</x:WindowTopY> <x:ProtectStructure>False</x:ProtectStructure> <x:ProtectWindows>False</x:ProtectWindows> </x:ExcelWorkbook> </xml><![endif]-->

[hb_gx]

这贴精彩，关注一下
我最近在学Excel和sqlserver之间的数据插入更新

这个地址不错
Excel精英培训网(论坛网址www.excelpx.com)

[alisafan123]

你的问题解决了吗