向大家请教一下，C#里面要把查询出来的数据条目导出到Excel，现在是可以导出来了，但是没有把字段名给显示出来，直接就显示数据了，而且只导出了一条，我想把查询的数据全部都导出来，而且导出的数据很乱，请问大家一下，导出的时候字段也让它显示出来，还有就是怎么样让导出的数据居中显示，根据导出的数据大小自动调整列宽？？
private void 导出ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出Excel文件到";
if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
return;
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312")); //gb2312 try
{
//写标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 0) dataGridView1.Columns[i].HeaderText = "序号";
if (i > 0)
{
std += "\t";
} std += dataGridView1.Columns[i].HeaderText;
} sw.WriteLine(str); //写内容 for (int j = 0; j < dataGridView1.Rows.Count - 1; j++)
{
tempStr = ""; for (int k = 0; k < dataGridView1.Columns.Count; k++)
{ if (k == 0 && j < (dataGridView1.Rows.Count - 2))
dataGridView1.Rows[j].Cells[k].Value = j + 1; if (k > 0)
{
tempStr += "\t";
}
tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
} sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
private void 导出ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出Excel文件到";
if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
return;
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312")); //gb2312 try
{
//写标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 0) dataGridView1.Columns[i].HeaderText = "序号";
if (i > 0)
{
std += "\t";
} std += dataGridView1.Columns[i].HeaderText;
} sw.WriteLine(str); //写内容 for (int j = 0; j < dataGridView1.Rows.Count - 1; j++)
{
tempStr = ""; for (int k = 0; k < dataGridView1.Columns.Count; k++)
{ if (k == 0 && j < (dataGridView1.Rows.Count - 2))
dataGridView1.Rows[j].Cells[k].Value = j + 1; if (k > 0)
{
tempStr += "\t";
}
tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
} sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
/// 导出Excel
/// </summary>
/// <param name="FileName"></param>
/// <param name="hfwebgrid"></param>
private void CreateExcel(string FileName, HFWebGrid hfwebgrid)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel"; resp.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls"); this.EnableViewState = false; string colHeaders = "", Is_item = "";
//定义表对象与行对象，同时使用DataSet对其值进行初始化 //DataRow[] myRow = dt.Select("");
//typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件 //取得数据表各列标题，标题之间以\t分割，最后一个列标题后加回车符
for (int i = 0; i < hfwebgrid.Bands[0].Columns.Count; i++)
{
if (!hfwebgrid.Bands[0].Columns[i].Hidden)
{
colHeaders += hfwebgrid.Bands[0].Columns[i].Header.Caption.ToString() + "\t";
}
}
colHeaders += "\n"; resp.Write(colHeaders); //逐行处理数据
for (int i = 0; i < hfwebgrid.Rows.Count; i++)
{ for (int g = 0; g < hfwebgrid.Bands[0].Columns.Count; g++)
{
if (!hfwebgrid.Rows[i].Cells[g].Column.Hidden)
{
Is_item += hfwebgrid.Rows[i].Cells[g].GetText() + "\t";
} }
Is_item += "\n"; } resp.Write(Is_item);
Is_item = "";
//写缓冲区中的数据到HTTP头文件中 resp.End(); }
/// 导出EXCEL
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
//-***************获取要写入excel的数据源*************** DataTable dt = getDataTableAll();
//-***************获取excel对象***************
string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName ="号牌查询结果 "+DateTime.Today.ToString("yyyy-MM-dd");
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.Workbook workbook = xlApp.Workbooks.Add(true);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range;
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = dt.Columns.Count;
int RowCount=dt.Rows.Count;
// *****************获取数据*********************
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount];
// 获取列标题
//foreach(DataGridViewColumn cs in dataGridView1.Columns)
//{
// objData[RowIndex,colIndex++] = cs.HeaderText;
//}
objData[RowIndex, 0] = "号牌号码";
objData[RowIndex, 1] = "车 主";
objData[RowIndex, 2] = "签发日期";
objData[RowIndex, 3] = "有效期至";
objData[RowIndex, 4] = "厂牌型号";
objData[RowIndex, 5] = "车架号";
objData[RowIndex, 6] = "保单号";
//objData[RowIndex, 7] = "备注";
// 获取具体数据
for(RowIndex =1;RowIndex<= RowCount;RowIndex++)
{
for(colIndex=0;colIndex < colCount;colIndex++)
{
if (colIndex == 6)
{
objData[RowIndex, colIndex] = "'" + dt.Rows[RowIndex - 1][colIndex];
}
else
{
objData[RowIndex, colIndex] = dt.Rows[RowIndex - 1][colIndex];
}
}
}
//********************* 写入Excel*******************
range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount+2,colCount]);
range.Value2= objData;
Application.DoEvents();
//*******************设置输出格式******************************
//设置顶部说明
range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,colCount]);
range.MergeCells = true;
range.Font.Bold=true;
range.Font.ColorIndex=10;//字体颜色
xlApp.ActiveCell.FormulaR1C1 = "号牌查询结果";
//特殊数字格式
//range = worksheet.get_Range(xlApp.Cells[2,colCount],xlApp.Cells[RowCount,colCount]);
//range.NumberFormat="yyyy-MM-dd hh:mm:ss";
xlApp.Cells.HorizontalAlignment=Excel.Constants.xlCenter;
range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[2,colCount]);
//range.Interior.ColorIndex = 10;//背景色
range.Font.Bold = true;
range.RowHeight=20;
//((Excel.Range)worksheet.Cells[2,1]).ColumnWidth=25;
//((Excel.Range)worksheet.Cells[2,2]).ColumnWidth=13;
((Excel.Range)worksheet.Cells[2,3]).ColumnWidth=24;
//((Excel.Range)worksheet.Cells[2,4]).ColumnWidth=15;
((Excel.Range)worksheet.Cells[2,6]).ColumnWidth=13;
((Excel.Range)worksheet.Cells[2,7]).ColumnWidth =25;
//***************************保存**********************
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();
xlApp.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();//强行销毁 //if(fileSaved && System.IO.File.Exists(saveFileName))
// System.Diagnostics.Process.Start(saveFileName);
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;namespace JGExam.BLL
{
public class ImportExportToExcel
{
private string strConn; private System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
private System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog(); public ImportExportToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; this.saveFileDlg.DefaultExt = "xls";
this.saveFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; }
#region 从Excel文件导入到DataSet
// /// <summary>
// /// 从Excel导入文件
// /// </summary>
// /// <param name="strExcelFileName">Excel文件名</param>
// /// <returns>返回DataSet</returns>
// public DataSet ImportFromExcel(string strExcelFileName)
// {
// return doImport(strExcelFileName);
// }
/**/
/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
{
DataSet ds = new DataSet();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
ds = doImport(openFileDlg.FileName);
return ds;
}
/**/
/// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">Excel文件名</param>
/// <returns></returns>
public DataSet ImportFromExcel(string strFileName)
{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
/**/
/// <summary>
/// 执行导入
/// </summary>
/// <param name="strFileName">文件名</param>
/// <returns>DataSet</returns>
public DataSet doImport(string strFileName)
{
if (strFileName == "") return null; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo"); }
catch (Exception err)
{
System.Console.WriteLine(err.ToString());
}
return ExcelDs; }
#endregion #region 从DataSet到出到Excel
/**/
/// <summary>
/// 导出指定的Excel文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的Excel文件名</param>
public void ExportToExcel(DataSet ds, string strExcelFileName)
{
if (ds.Tables.Count == 0 || strExcelFileName == "") return;
doExport(ds, strExcelFileName);
}
/**/
/// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds, saveFileDlg.FileName); }
/**/
/// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
private void doExport(DataSet ds, string strExcelFileName)
{ Excel.Application excel = new Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex = 1;
int colIndex = 0; excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds.Tables[0];
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
} foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false; excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
Excel.XlFileFormat.xlExcel9795, null, null, false,
false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel = null; GC.Collect();//垃圾回收
}
#endregion #region 从XML导入到Dataset /**/
/// <summary>
/// 从选择的XML文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromXML()
{
DataSet ds = new DataSet();
System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
openFileDlg.DefaultExt = "xml";
openFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
try { ds.ReadXml(openFileDlg.FileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
}
/**/
/// <summary>
/// 从指定的XML文件导入
/// </summary>
/// <param name="strFileName">XML文件名</param>
/// <returns></returns>
public DataSet ImportFromXML(string strFileName)
{
if (strFileName == "")
return null;
DataSet ds = new DataSet();
try { ds.ReadXml(strFileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
} #endregion #region 从DataSet导出到XML
/**/
/// <summary>
/// 导出指定的XML文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strXMLFileName">要导出的XML文件名</param>
public void ExportToXML(DataSet ds, string strXMLFileName)
{
if (ds.Tables.Count == 0 || strXMLFileName == "") return;
doExportXML(ds, strXMLFileName);
}
/**/
/// <summary>
/// 导出用户选择的XML文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToXML(DataSet ds)
{
System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt = "xml";
saveFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExportXML(ds, saveFileDlg.FileName);
} /**/
/// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的XML文件名</param>
private void doExportXML(DataSet ds, string strXMLFileName)
{
try
{ ds.WriteXml(strXMLFileName, System.Data.XmlWriteMode.WriteSchema); }
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.Message, "Errol"); }
} #endregion
}
}