如何将DataSet 导出并形成相应的Execl文件?能够加中文列头 如何将DataSet 导出并形成相应的Execl文件?能够加中文列头 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /// <summary> /// 向EXCEL表格里插入数据库记录 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExcel_Click(object sender, System.EventArgs e) { //查询数据库内容判断 AccFlage的值,如果其值是1则用'√'替换,如果是0则为空 string query_sql = "select AccDate,No,Abr_AccId,Abr_Content,Abr_Person,(select case when AccFlag = 1 then '√' when AccFlag = 0 then ''else ''end as 'AccFlag'),DbtAmt,CrdAmt,EndAmt from accounttype where accno = '" + cboNo.SelectedValue + "'and AccDate between '" + dateStart.Value.Date + "' and '" + dateEnd.Value.Date + "'order by NO"; SqlConnection con = new SqlConnection(ConStr); SqlDataAdapter da = new SqlDataAdapter(query_sql,con); DataSet ds = new DataSet(); da.Fill(ds,"accounttype"); string query = "select endamt from accounttype where id = '" + this.txtDate.Text + "'"; SqlDataAdapter daNow = new SqlDataAdapter(query,con); DataSet dsNow = new DataSet(); daNow.Fill(dsNow,"accounttype"); txtNow.DataBindings.Clear(); txtNow.DataBindings.Add("Text",dsNow,"AccountType.EndAmt"); string str; if(this.radioMoney.Checked) { str = "0"; } else { str = "1"; } if(str == "0") { string query_money = "select * from money where MoneyAttr = 2"; SqlDataAdapter damoney = new SqlDataAdapter(query_money,con); DataSet dsmoney = new DataSet(); damoney.Fill(dsmoney,"money"); txtMoney.DataBindings.Clear(); txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money"); } else { string query_money = "select * from money where MoneyAttr = 3"; SqlDataAdapter damoney = new SqlDataAdapter(query_money,con); DataSet dsmoney = new DataSet(); damoney.Fill(dsmoney,"money"); txtMoney.DataBindings.Clear(); txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money"); } string filename=""; //将模板文件复制到一个新文件中 SaveFileDialog mySave = new SaveFileDialog(); mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*"; if(mySave.ShowDialog()!=DialogResult.OK) { return; } else { filename = mySave.FileName; //将模板文件copy到新位置,建议实际开发时用相对路径,如Application.StartupPath.Trim()+"\\report\\normal.xls" FileInfo mode=new FileInfo(Application.StartupPath.Trim() + @"\Report\CashReport.xls"); try { mode.CopyTo(filename,true); } catch(Exception ex) { MessageBox.Show(ex.Message); return; } } //打开复制后的文件 object missing = Missing.Value; Excel.Application myExcel = new Excel.Application ( ); //打开新文件 myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing); //将Excel显示出来 myExcel.Visible=true; //将列标题和实际内容选中 Excel.Workbook myBook = myExcel.Workbooks[1]; Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1]; mySheet.Cells[2,5] = this.dateStart.Value.Date; mySheet.Cells[2,8] = this.dateEnd.Value.Date; if(str == "0") { mySheet.Cells[1,1] = "现金存款日记帐------" + this.cboNo.SelectedValue; } else { mySheet.Cells[1,1] = "银行存款日记帐------" + this.cboNo.SelectedValue; } //判断是否有余额,如果没有就把初期额添加入EXCEL中 if(this.txtDate.Text == "") { mySheet.Cells[5,9] = txtMoney.Text.ToString(); } else { mySheet.Cells[5,9] = txtNow.Text.ToString(); } //向EXCEL里插记录 int HeadLines=5; int j=0; for(int r = 0;r<ds.Tables[0].Rows.Count;r++) { if (((r+1) % 22)==0) { for(int i = 0;i<ds.Tables[0].Columns.Count;i++) { mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i]; } mySheet.Cells[j*2+r+HeadLines+2,4] = "过次页"; mySheet.Cells[j*2+r+HeadLines+3,4] = "承上页"; mySheet.Cells[j*2+r+HeadLines+3,9] = ds.Tables[0].Rows[r][8];; j++; } else { for(int i = 0;i<ds.Tables[0].Columns.Count;i++) { mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i]; } } } } 通用一点的:public void ExportToExcel(string filename, System.Data .DataTable dt,string excelname) { if(dt==null) return; string saveFileName=""; bool fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName =filename; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0) return; //被点了取消 Excel.Application xlApp=new Excel.Application(); if(xlApp==null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Excel.Workbooks workbooks=xlApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Excel.Range range; long totalCount=dt.Rows.Count; long rowRead=0; float percent=0; worksheet.Cells[1,1]=excelname; //写入字段 for(int i=0;i<dt.Columns.Count;i++) { worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[2,i+1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 //this.CaptionVisible = true; for(int r=0;r<dt.Rows.Count;r++) { for(int i=0;i<dt.Columns.Count;i++) { worksheet.Cells[r+3,i+1]=dt.Rows[r][i]; } rowRead++; percent=((float)(100*rowRead))/totalCount; //this.CaptionText = "正在导出数据["+ percent.ToString("0.00") +"%]..."; System.Windows.Forms .Application.DoEvents(); } //this.CaptionVisible = false; //this.CaptionText = oldCaption; range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; if(dt.Columns.Count>1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if(saveFileName!="") { try { workbook.Saved =true; workbook.SaveCopyAs(saveFileName); fileSaved=true; } catch(Exception ex) { fileSaved=false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n"+ex.Message); } } else { fileSaved=false; } xlApp.Quit(); GC.Collect();//强行销毁 if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); } public void ExportToExcel(string filename, System.Data .DataTable dt,string excelname)解释如下:filename: 就是你要的Excel中的中文表头dt:要导出的DataTableexcelname:要保存的excel文件名称 fifadeke(咖啡淡了) 你好,虽然我不是楼主,可刚好也遇到这个问题。 如有空也帮忙传我一份,谢谢~~ 导出时用查询分析器,并且SQL语句里列名用别名--示例:select a as 列A,b as 列B from 表 http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50 窗体改变大小时边框附件出现闪烁的黑色 vs2005 treeview 控件能自动根据XML文件,生成节点吗? 线程中修改 DEV TreeList 的问题 请高手给我提供一些关于asp.net页面速度优化的建议 ZedGraph 控件 Y轴的问题 C#调用C++编写的DLL中方法问题(方法中有传回参数) 动态绑定数据自动生成的datagrid,如何实现翻页、排序呢? 水晶报表设计时如何打开显示字段浏览器窗口 求翻译!!! OPC UA官方文档翻译 请问:如何在C#中点击一个按钮后,弹出“数据连接属性”对话筐? 关于编译执行的问题?
/// 向EXCEL表格里插入数据库记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, System.EventArgs e)
{
//查询数据库内容判断 AccFlage的值,如果其值是1则用'√'替换,如果是0则为空
string query_sql = "select AccDate,No,Abr_AccId,Abr_Content,Abr_Person,(select case when AccFlag = 1 then '√' when AccFlag = 0 then ''else ''end as 'AccFlag'),DbtAmt,CrdAmt,EndAmt from accounttype where accno = '" + cboNo.SelectedValue + "'and AccDate between '" + dateStart.Value.Date + "' and '" + dateEnd.Value.Date + "'order by NO";
SqlConnection con = new SqlConnection(ConStr);
SqlDataAdapter da = new SqlDataAdapter(query_sql,con);
DataSet ds = new DataSet();
da.Fill(ds,"accounttype"); string query = "select endamt from accounttype where id = '" + this.txtDate.Text + "'";
SqlDataAdapter daNow = new SqlDataAdapter(query,con);
DataSet dsNow = new DataSet();
daNow.Fill(dsNow,"accounttype");
txtNow.DataBindings.Clear();
txtNow.DataBindings.Add("Text",dsNow,"AccountType.EndAmt"); string str;
if(this.radioMoney.Checked)
{
str = "0";
}
else
{
str = "1";
} if(str == "0")
{
string query_money = "select * from money where MoneyAttr = 2";
SqlDataAdapter damoney = new SqlDataAdapter(query_money,con);
DataSet dsmoney = new DataSet();
damoney.Fill(dsmoney,"money");
txtMoney.DataBindings.Clear();
txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money");
}
else
{
string query_money = "select * from money where MoneyAttr = 3";
SqlDataAdapter damoney = new SqlDataAdapter(query_money,con);
DataSet dsmoney = new DataSet();
damoney.Fill(dsmoney,"money");
txtMoney.DataBindings.Clear();
txtMoney.DataBindings.Add("Text",dsmoney,"Money.Money");
}
string filename="";
//将模板文件复制到一个新文件中
SaveFileDialog mySave = new SaveFileDialog();
mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
if(mySave.ShowDialog()!=DialogResult.OK)
{
return;
}
else
{
filename = mySave.FileName;
//将模板文件copy到新位置,建议实际开发时用相对路径,如Application.StartupPath.Trim()+"\\report\\normal.xls"
FileInfo mode=new FileInfo(Application.StartupPath.Trim() + @"\Report\CashReport.xls");
try
{
mode.CopyTo(filename,true);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
//打开复制后的文件
object missing = Missing.Value;
Excel.Application myExcel = new Excel.Application ( );
//打开新文件
myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
//将Excel显示出来
myExcel.Visible=true;
//将列标题和实际内容选中
Excel.Workbook myBook = myExcel.Workbooks[1];
Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];
mySheet.Cells[2,5] = this.dateStart.Value.Date;
mySheet.Cells[2,8] = this.dateEnd.Value.Date;
if(str == "0")
{
mySheet.Cells[1,1] = "现金存款日记帐------" + this.cboNo.SelectedValue;
}
else
{
mySheet.Cells[1,1] = "银行存款日记帐------" + this.cboNo.SelectedValue;
}
//判断是否有余额,如果没有就把初期额添加入EXCEL中
if(this.txtDate.Text == "")
{
mySheet.Cells[5,9] = txtMoney.Text.ToString();
}
else
{
mySheet.Cells[5,9] = txtNow.Text.ToString();
}
//向EXCEL里插记录
int HeadLines=5;
int j=0; for(int r = 0;r<ds.Tables[0].Rows.Count;r++)
{
if (((r+1) % 22)==0)
{
for(int i = 0;i<ds.Tables[0].Columns.Count;i++)
{
mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i];
}
mySheet.Cells[j*2+r+HeadLines+2,4] = "过次页";
mySheet.Cells[j*2+r+HeadLines+3,4] = "承上页";
mySheet.Cells[j*2+r+HeadLines+3,9] = ds.Tables[0].Rows[r][8];;
j++;
}
else
{
for(int i = 0;i<ds.Tables[0].Columns.Count;i++)
{
mySheet.Cells[j*2+r+HeadLines+1,i+1] = ds.Tables[0].Rows[r][i];
}
}
}
}
{
if(dt==null) return; string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName =filename;
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0) return; //被点了取消
Excel.Application xlApp=new Excel.Application(); if(xlApp==null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
} Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;
long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0; worksheet.Cells[1,1]=excelname;
//写入字段
for(int i=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
//this.CaptionVisible = true;
for(int r=0;r<dt.Rows.Count;r++)
{
for(int i=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[r+3,i+1]=dt.Rows[r][i];
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;
//this.CaptionText = "正在导出数据["+ percent.ToString("0.00") +"%]...";
System.Windows.Forms .Application.DoEvents();
}
//this.CaptionVisible = false;
//this.CaptionText = oldCaption; range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; if(dt.Columns.Count>1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
} if(saveFileName!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开!\n"+ex.Message);
}
}
else
{
fileSaved=false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
}
解释如下:
filename: 就是你要的Excel中的中文表头
dt:要导出的DataTable
excelname:要保存的excel文件名称
你好,虽然我不是楼主,可刚好也遇到这个问题。
如有空也帮忙传我一份,谢谢~~
select a as 列A,b as 列B from 表