整理的 C# DataSet导出至Excel文件
//方法1
public bool SavetoExcel(string Path, DataSet ds)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
int RowCount = ds.Tables[0].Rows.Count;
string strfiles = "";
for (int i = 1; i <= ds.Tables[0].Columns.Count; i++)
{
strfiles = strfiles + "A" + i.ToString() + " ,";
}
for (int i = 0; i < RowCount; i++)
{
string strValues = "";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
strValues = strValues + " '" + ds.Tables[0].Rows[i][j].ToString() + "' ,";
}
//cmd.CommandText = "INSERT INTO [sheet1$] VALUES("+strValues.TrimEnd(',') +" )";
cmd.CommandText = "INSERT INTO [sheet1$](" + strfiles.TrimEnd(',') + ") select " + strValues.TrimEnd(',') + " ";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.Dispose();
return true;
}
catch (System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine("写入Excel发生错误:" + ex.Message);
}
return false;
}
* */
#endregion
//方法2 导出 EXCEL 文件
protected void ExportExcel(string strExportFilename, DataSet dsResultData)
{
if (dsResultData == null) return;
string saveFileName = strExportFilename;
Excel.Application xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value;
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
// string oldCaption = Title_label.Text.Trim();
long totalCount = dsResultData.Tables[0].Rows.Count;
for (int i = 0; i < dsResultData.Tables[0].Columns.Count; i++)
{
int IndexCol = arrColumn.IndexOf(dsResultData.Tables[0].Columns[i].Caption );
if (IndexCol > -1)
{
worksheet.Cells[1, i + 1] = arrColumnName[IndexCol ];// dsResultData.Tables[0].Columns[i].ColumnName;
}
}
//写入数值
// System.Windows.Forms.VisualStyles.VisualStyleElement.Window.Caption.Visible = true;
for (int r = 0; r < dsResultData.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < dsResultData.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dsResultData.Tables[0].Rows[r][i].ToString();
}
Application.DoEvents();
}
worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing);
//this.Caption.Visible = false;
//this.Caption.Text = oldCaption;
//range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dsResultData.Tables[0].Rows.Count + 2, dsResultData.Tables[0].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 (dsResultData.Tables[0].Columns.Count > 1)
//{
// range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
//}
workbook.Close(missing, missing, missing);
xlApp.Quit();
}
-------------------------
给自己淘宝小店做个广告:新上品质超好的婴幼儿用品,大家快来看看哦,针对老顾客全部进价销售!
http://lovehealthylife.taobao.com/
主营孕妇装,防辐射装,婴幼儿用品,自用送人都很合适!虽然没有搞折扣优惠之类的活动,但绝对质优价廉!比搞活动的还便宜!你懂滴