StringBuilder sb = new StringBuilder(); StringWriter sw = new StringWriter(sb); HtmlTextWriter htw = new HtmlTextWriter(sw); Page page = new Page(); HtmlForm form = new HtmlForm(); GridView1.EnableViewState = false; // Deshabilitar la validación de eventos, sólo asp.net 2 page.EnableEventValidation = false; // Realiza las inicializaciones de la instancia de la clase Page que requieran los diseñadores RAD. page.DesignerInitialize(); page.Controls.Add(form); form.Controls.Add(GridView1); page.RenderControl(htw); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=文件名称.xls"); Response.Charset = "UTF-8"; Response.ContentEncoding = Encoding.UTF8; Response.Write(sb.ToString()); Response.End();上边的这个函数可以把GridView中的数据导出到Excel当中,希望对你有帮助
Excel.Application xls_exp=null;
int rowindex=1;
int colindex=0;
//创建一个workbook,一个worksheet
Excel._Workbook xls_book=null;
Excel._Worksheet xls_sheet=null;
try
{
xls_exp=new Excel.ApplicationClass();
xls_book=xls_exp.Workbooks.Add(true);
xls_sheet=(Excel._Worksheet)xls_book.ActiveSheet;
//取得数据
DataTable aa=GetData();
//将所得到的表的列名,赋值给单元格
foreach(DataColumn col in aa.Columns)
{
colindex++;
xls_exp.Cells[1,colindex]=col.ColumnName;
//水平对齐
xls_sheet.get_Range(xls_exp.Cells[1,colindex],xls_exp.Cells[1,colindex]).HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
//垂直对齐
xls_sheet.get_Range(xls_exp.Cells[1,colindex],xls_exp.Cells[1,colindex]).VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;
//行高、列宽自适应
//xls_sheet.Cells.Rows.AutoFill();
//xls_sheet.Cells.Columns.AutoFill();
}
//同样方法处理数据
foreach(DataRow row in aa.Rows)
{
rowindex++;
colindex=0;
foreach(DataColumn col in aa.Columns)
{
colindex++;
switch (row[col.ColumnName].GetType().ToString())
{
//字符
case ("System.String"):
//数字格式设置为文本
xls_sheet.get_Range(xls_exp.Cells[rowindex,colindex],xls_exp.Cells[rowindex,colindex]).NumberFormatLocal="@";
//水平对齐
xls_sheet.get_Range(xls_exp.Cells[rowindex,colindex],xls_exp.Cells[rowindex,colindex]).HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;
//垂直对齐
xls_sheet.get_Range(xls_exp.Cells[rowindex,colindex],xls_exp.Cells[rowindex,colindex]).VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;
break;
//日期
case("System.DateTime"):
//数字格式设置为yyyy-mm-dd hh:mm:ss日期
xls_sheet.get_Range(xls_exp.Cells[rowindex,colindex],xls_exp.Cells[rowindex,colindex]).NumberFormatLocal="YYYY-MM-DD HH:MM:SS";
break;
}
//给cell赋值
xls_exp.Cells[rowindex,colindex]=row[col.ColumnName];
}
}
//不可见,即后台处理
xls_exp.Visible=true;
catch(Exception err)
{
MessageBox.show(err.Message);
}
//finally
//{
//xls_exp.Quit();
//}
exec xp_cmdshell 'bcp "sql查询语句" queryout "d:\test.xls" -c -q -S ".\SQLExpress" -U "sa" -P "123"'
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw); Page page = new Page();
HtmlForm form = new HtmlForm(); GridView1.EnableViewState = false; // Deshabilitar la validación de eventos, sólo asp.net 2
page.EnableEventValidation = false; // Realiza las inicializaciones de la instancia de la clase Page que requieran los diseñadores RAD.
page.DesignerInitialize(); page.Controls.Add(form);
form.Controls.Add(GridView1); page.RenderControl(htw); Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=文件名称.xls");
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.UTF8;
Response.Write(sb.ToString());
Response.End();上边的这个函数可以把GridView中的数据导出到Excel当中,希望对你有帮助
{
DataTable dt = new DataTable();
DataColumn xm = new DataColumn("xm", Type.GetType("System.String"));
DataColumn sex= new DataColumn("sex",Type.GetType("System.String"));
...
dt.Columns.Add(xm);
dt.Columns.Add(sex);
..... //从数据库读内容部分就略掉了
DataTable source = ...;
if (source.Rows.Count > 0)
{
for (int i = 0; i < source.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr["xm"] = source.Rows [i]["xm"].ToString();
dr["sex"] = source.Rows [i]["sex"].ToString(); ....
dt.Rows.Add(dr);
}
}
dt.Columns[0].ColumnName = "姓名";
dt.Columns[1].ColumnName = "性别";
...
if (dt.Rows.Count > 0)
{
string path = Server.MapPath("~") + "/student/学生表.xls";
ExportExcel(dt, path);
}
Response.Redirect("学生表.xls");
}
private void ExportExcel(DataTable stu_dt, string filename)
{
if (File.Exists(filename))
{
File.Delete(filename);
//存在文件删除
}
string filepath = filename;
DataTable dt = stu_dt;
FileStream objstream;
StreamWriter objwriter;
string strLine = "";
objstream = new FileStream(filepath, FileMode.Create, FileAccess.Write);
objwriter = new StreamWriter(objstream, System.Text.Encoding.Unicode); for (int i = 0; i < dt.Columns.Count; i++)
{
strLine += dt.Columns[i].ColumnName.ToString() + Convert.ToChar(9);
}
objwriter.WriteLine(strLine);
strLine = ""; for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
strLine += dt.Rows[i][j].ToString() + Convert.ToChar(9);
}
objwriter.WriteLine(strLine);
strLine = "";
}
objwriter.Close();
objstream.Close();
}
/// 生成EXCEL文件
/// _til是表格的标题,_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
/// </summary>
public void SaveToExcel(string _til, int _row, int _col, DataTable _dt, string _filename)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
//合并单元格
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);
// xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
xlsheet.ActiveSheet.Cells[1, 1] = _til;
//字体加粗
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Bold(true);
//单元格文本水平居中对齐
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
//设置字体大小
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Size(14);
//设置列宽
//xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50); //画边框线
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous); //定义一个2维数组用来存储DATATABLE里的数据
object[,] dataArray = new object[_row, _col];
//添加列名
for (int k = 0; k < _dt.Columns.Count; k++)
{
xlsheet.ActiveSheet.Cells[2, k + 1] = _dt.Columns[k].Caption.ToString();
}
//把DATATABLE里的数据导到2维数组中
for (int i = 0; i < _row; i++)
{
for (int j = 0; j < _col; j++)
{
dataArray[i, j] = _dt.Rows[i][j];
}
}
//把2维数组中的数据导到EXCEL中
xlsheet.get_Range("A3", xlsheet.Cells[_row + 2, _col]).Value2 = dataArray;
xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
}
/// <summary>
/// 导入EXCEL表
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public DataSet CreateDataSource(string strFileName, string Sqlstr)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);
DataSet myds = new DataSet();
myda.Fill(myds);
return myds;
}