VS2005如何把数据输出成为Excel报表 请问:VS2005如何把数据输出成为Excel报表?解答的傻瓜点最好,一直没有闹明白,今天要用到这一块了所以很头疼,麻烦大家了。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 网上一搜一大把,这是WINFORM下的一个//导出到EXCEL private void button1_Click(object sender, EventArgs e) { try { Excel.ApplicationClass excel = new Excel.ApplicationClass(); excel.Application.Workbooks.Add(true); excel.Visible = true; Worksheet sheet = ((Worksheet)excel.ActiveWorkbook.Sheets[1]); int iRowCount = dataGridView1.Rows.Count; int iColCount = dataGridView1.ColumnCount; for (int k = 0; k < iColCount; k++) { excel.Cells[1, k + 1] = dataGridView1.Columns[k].HeaderText; } for (int i = 0; i < iRowCount; i++) { for (int j = 0; j < iColCount; j++) { excel.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value; //未匹配的EXCEL单元格变色 if ((1 == j) && ("未匹配" == dataGridView1.Rows[i].Cells[j].Value.ToString())) { sheet.get_Range(sheet.Cells[i + 2, j + 1], sheet.Cells[i + 2, j + 1]).Font.Color = System.Drawing.Color.Blue.ToArgb(); } } } ((MainForm)(Parent.Parent)).SetStateText("数据导出完毕。"); //MessageBox.Show("数据导出完毕。"); } catch (Exception ex) { ((MainForm)(Parent.Parent)).SetStateText("数据导出异常:" + ex.Message); } } public void getExcelFile(DataTable dtData,DataTable dtHeader,string FileName,System.Web.UI.Page Page) { HttpResponse resp=Page.Response; resp.Clear(); resp.Buffer= true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls"); resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 )); resp.ContentType="application/ms-excel"; string colHeaders= "", ls_item=""; int i=0; //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 foreach(DataRow row in dtHeader.Rows) colHeaders+=ReplaceEnter(row[0].ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for(i=0;i<dtData.Columns.Count;i++) { if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate") { if(row[i].ToString().Trim().Length>0) ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; else ls_item +=" "+"\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; } else ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); } public void getExcelFile(DataTable dtData,string FileName,System.Web.UI.Page Page) { HttpResponse resp=Page.Response; resp.Clear(); resp.Buffer= true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls"); resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 )); resp.ContentType= "application/ms-excel"; string colHeaders= "", ls_item=""; int i=0; //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 for(i=0;i<dtData.Columns.Count;i++) colHeaders+=ReplaceEnter(dtData.Columns[i].Caption.ToString())+"\t"; colHeaders +="\n"; //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach(DataRow row in dtData.Rows) { //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for(i=0;i<dtData.Columns.Count;i++) { if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate") { if(row[i].ToString().Trim().Length>0) ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t"; else ls_item +=" "+"\t"; } else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate") { ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t"; } else ls_item +=ReplaceEnter(row[i].ToString()) + "\t"; } ls_item +="\n"; //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 resp.Write(ls_item); ls_item=""; } resp.End(); } string fileExcel, filePath, fileName, strLine, sql; FileStream objFileStream; StreamWriter objStreamWriter; Random nRandom = new Random(DateTime.Now.Millisecond); SqlConnection cnn = new SqlConnection("data source=(local);initial catalog=chinapackage;user id=sa;password=;"); fileExcel = "t" + nRandom.Next().ToString() + ".xls"; filePath = Server.MapPath("\\StartExcel"); fileName = filePath + "\\" + fileExcel; objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write); objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode); cnn.Open(); sql = "select * from tbdownload"; SqlCommand cmd = new SqlCommand(sql, cnn); SqlDataReader dr; dr = cmd.ExecuteReader(); strLine = ""; for (int i = 0; i <= dr.FieldCount-1; i++) { strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine = ""; while (dr.Read()) { for (int i = 0; i <= dr.FieldCount-1; i++) { strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine=""; } dr.Close(); cnn.Close(); objStreamWriter.Close(); objFileStream.Close(); 修改已发布的资料出现web.config配置错误 LINQ to Entities 不识别方法“System.String ToString(System.String)”因此该方法无法转换为存储表达式 救命啊。很严重! 物流单查询,怎么抓取查询 时间问题 如何接口实例化................... 没有动态设计的基础,也是刚学ASP。NET 有一些问题?谢谢朋友们回答我!~~~!!! 文字在网页中显示是乱码!!!如何解决??? 截取物理路径问题 急求:如何用JavaScript传递参数 怎样读取EXCEL某个单元格的内容? 帮忙把以下代码改成泛型的
private void button1_Click(object sender, EventArgs e)
{
try
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
excel.Visible = true; Worksheet sheet = ((Worksheet)excel.ActiveWorkbook.Sheets[1]); int iRowCount = dataGridView1.Rows.Count;
int iColCount = dataGridView1.ColumnCount; for (int k = 0; k < iColCount; k++)
{
excel.Cells[1, k + 1] = dataGridView1.Columns[k].HeaderText;
} for (int i = 0; i < iRowCount; i++)
{
for (int j = 0; j < iColCount; j++)
{
excel.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value; //未匹配的EXCEL单元格变色
if ((1 == j) && ("未匹配" == dataGridView1.Rows[i].Cells[j].Value.ToString()))
{
sheet.get_Range(sheet.Cells[i + 2, j + 1], sheet.Cells[i + 2, j + 1]).Font.Color = System.Drawing.Color.Blue.ToArgb();
}
}
} ((MainForm)(Parent.Parent)).SetStateText("数据导出完毕。");
//MessageBox.Show("数据导出完毕。");
}
catch (Exception ex)
{
((MainForm)(Parent.Parent)).SetStateText("数据导出异常:" + ex.Message);
}
}
public void getExcelFile(DataTable dtData,DataTable dtHeader,string FileName,System.Web.UI.Page Page)
{
HttpResponse resp=Page.Response; resp.Clear();
resp.Buffer= true;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 ));
resp.ContentType="application/ms-excel";
string colHeaders= "", ls_item="";
int i=0;
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
foreach(DataRow row in dtHeader.Rows)
colHeaders+=ReplaceEnter(row[0].ToString())+"\t";
colHeaders +="\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in dtData.Rows)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for(i=0;i<dtData.Columns.Count;i++)
{
if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate")
{
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t";
}
else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate")
{
if(row[i].ToString().Trim().Length>0)
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t";
else
ls_item +=" "+"\t";
}
else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate")
{
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t";
}
else
ls_item +=ReplaceEnter(row[i].ToString()) + "\t";
}
ls_item +="\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item="";
}
resp.End();
}
public void getExcelFile(DataTable dtData,string FileName,System.Web.UI.Page Page)
{
HttpResponse resp=Page.Response; resp.Clear();
resp.Buffer= true;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
resp.AddHeader("Content-disposition","attachment; filename="+HttpUtility.UrlEncode(FileName+".xls",Encoding.UTF8 ));
resp.ContentType= "application/ms-excel";
string colHeaders= "", ls_item="";
int i=0;
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for(i=0;i<dtData.Columns.Count;i++)
colHeaders+=ReplaceEnter(dtData.Columns[i].Caption.ToString())+"\t";
colHeaders +="\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in dtData.Rows)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for(i=0;i<dtData.Columns.Count;i++)
{
if(dtData.Columns[i].ColumnName.Trim().ToLower()=="bookingdate")
{
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t";
}
else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="cancellationdate")
{
if(row[i].ToString().Trim().Length>0)
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss")+ "\t";
else
ls_item +=" "+"\t";
}
else if(dtData.Columns[i].ColumnName.Trim().ToLower()=="arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower()=="departuredate")
{
ls_item +=" "+DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd")+ "\t";
}
else
ls_item +=ReplaceEnter(row[i].ToString()) + "\t";
}
ls_item +="\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item="";
}
resp.End();
}
FileStream objFileStream;
StreamWriter objStreamWriter;
Random nRandom = new Random(DateTime.Now.Millisecond);
SqlConnection cnn = new SqlConnection("data source=(local);initial catalog=chinapackage;user id=sa;password=;");
fileExcel = "t" + nRandom.Next().ToString() + ".xls";
filePath = Server.MapPath("\\StartExcel");
fileName = filePath + "\\" + fileExcel;
objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
cnn.Open();
sql = "select * from tbdownload";
SqlCommand cmd = new SqlCommand(sql, cnn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
strLine = "";
for (int i = 0; i <= dr.FieldCount-1; i++)
{
strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9);
}
objStreamWriter.WriteLine(strLine);
strLine = "";
while (dr.Read())
{
for (int i = 0; i <= dr.FieldCount-1; i++)
{
strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9);
}
objStreamWriter.WriteLine(strLine);
strLine="";
}
dr.Close();
cnn.Close();
objStreamWriter.Close();
objFileStream.Close();