后台：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.IO;
using DevExpress.XtraEditors;
namespace export
{
//dataset导出excel类，若dataset中存在多个datatable，该类可将其导出在
//同一个exce的多个sheet，使用时注意引用using Microsoft.Office.Interop.Excel;
//将using Microsoft.Office.Interop.Excel属性中的嵌入互操作类型改为false
//含进度条
public class ToExcelSheet
{
public void DataSet2Sheet(DataSet ds, string fileName, ProgressBarControl pbcontorl)
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "2003Excel|*.xls|2007Excel|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
fileName = saveDialog.FileName;
if (fileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application appExcel;
appExcel = new Microsoft.Office.Interop.Excel.Application();
if (appExcel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbook workbookData;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
Range range;
workbookData = appExcel.Workbooks.Add(System.Reflection.Missing.Value);
appExcel.DisplayAlerts = false;//不显示警告
//xlApp.Visible = true;//excel是否可见
//
//for (int i = workbookData.Worksheets.Count; i > 0; i--)
//{
// Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(i);
// oWorksheet.Select();
// oWorksheet.Delete();
//}
int introwcount = ds.Tables[0].Rows.Count;
//ProgressBar pb = new ProgressBar();
pbcontorl.Properties.Minimum = 0;
pbcontorl.Properties.Maximum = introwcount;
pbcontorl.Visible = true;
long rowRead = 0;
float percent = 0;
pbcontorl.Properties.ProgressViewStyle = DevExpress.XtraEditors.Controls.ProgressViewStyle.Solid;
for (int k = 0; k < ds.Tables.Count; k++)
{
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
// testnum--;
if (ds.Tables[k] != null)
{
worksheetData.Name = ds.Tables[k].TableName;
//写入标题
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[1, i + 1] = ds.Tables[k].Columns[i].ColumnName;
range = (Range)worksheetData.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.NumberFormatLocal = "@";//文本格式
// range.EntireColumn.AutoFit();//自动调整列宽
// range.WrapText = true; //文本自动换行
range.ColumnWidth = 15;
}
//写入数值
for (int r = 0; r < ds.Tables[k].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[r + 2, i + 1] = ds.Tables[k].Rows[r][i];
//Range myrange = worksheetData.get_Range(worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]);
//myrange.NumberFormatLocal = "@";//文本格式
//// myrange.EntireColumn.AutoFit();//自动调整列宽
//// myrange.WrapText = true; //文本自动换行
//myrange.ColumnWidth = 15;
}
// rowRead++;
rowRead++;
percent = ((float)(100 * rowRead)) / introwcount;//进度条控件需要用百分号表示进度时,使用该行代码
pbcontorl.Properties.Step = (int)rowRead;//设置进度条控件的当前值
pbcontorl.PerformStep();
System.Windows.Forms.Application.DoEvents();
}
}
worksheetData.Columns.EntireColumn.AutoFit();
workbookData.Saved = true;
}
workbookData.SaveAs(fileName);
workbookData.Close();
appExcel.Quit();
DialogResult btn = MessageBox.Show(null, "文件“" + fileName + "”保存成功。是否现在打开？", "信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (btn == DialogResult.Yes)
{
ApplicationClass excelApp = new ApplicationClass();
Workbook workbook2 = excelApp.Workbooks.Open(fileName);
excelApp.Visible = true;
}
// appExcel.Quit();
GC.Collect();
}
//不含进度条
public void DataSet2Sheet(DataSet ds, string fileName)
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "2003Excel|*.xls|2007Excel|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
fileName = saveDialog.FileName;
if (fileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application appExcel;
appExcel = new Microsoft.Office.Interop.Excel.Application();
if (appExcel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbook workbookData;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
Range range;
workbookData = appExcel.Workbooks.Add(System.Reflection.Missing.Value);
appExcel.DisplayAlerts = false;//不显示警告
//xlApp.Visible = true;//excel是否可见
//
//for (int i = workbookData.Worksheets.Count; i > 0; i--)
//{
// Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(i);
// oWorksheet.Select();
// oWorksheet.Delete();
//}
for (int k = 0; k < ds.Tables.Count; k++)
{
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
// testnum--;
if (ds.Tables[k] != null)
{
worksheetData.Name = ds.Tables[k].TableName;
//写入标题
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[1, i + 1] = ds.Tables[k].Columns[i].ColumnName;
range = (Range)worksheetData.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.NumberFormatLocal = "@";//文本格式
// range.EntireColumn.AutoFit();//自动调整列宽
// range.WrapText = true; //文本自动换行
range.ColumnWidth = 15;
}
//写入数值
for (int r = 0; r < ds.Tables[k].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[r + 2, i + 1] = ds.Tables[k].Rows[r][i];
//Range myrange = worksheetData.get_Range(worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]);
//myrange.NumberFormatLocal = "@";//文本格式
//// myrange.EntireColumn.AutoFit();//自动调整列宽
//// myrange.WrapText = true; //文本自动换行
//myrange.ColumnWidth = 15;
}
// rowRead++;
System.Windows.Forms.Application.DoEvents();
}
}
worksheetData.Columns.EntireColumn.AutoFit();
workbookData.Saved = true;
}
workbookData.SaveAs(fileName);
workbookData.Close();
appExcel.Quit();
DialogResult btn = MessageBox.Show(null, "文件“" + fileName + "”保存成功。是否现在打开？", "信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (btn == DialogResult.Yes)
{
ApplicationClass excelApp = new ApplicationClass();
Workbook workbook2 = excelApp.Workbooks.Open(fileName);
excelApp.Visible = true;
}
// appExcel.Quit();
GC.Collect();
}
}
}
前台：
#region 导出酒席预定excel表格
private void simpleButton5_Click(object sender, EventArgs e)
{
DataSet ds = feast.SearchFeastBook(fshopid); //读取当前酒席预定表格数据
ds.Tables[0].Columns.Remove("FID"); //移除不需要导出的excel的字段
ds.Tables[0].Columns.Remove("FBillID");
ds.Tables[0].Columns.Remove("FFeastNo");
ds.Tables[0].Columns.Remove("FFeastShortName");
ds.Tables[0].Columns.Remove("FTCID");
ds.Tables[0].Columns.Remove("FTotalAmt");
ds.Tables[0].Columns.Remove("FCustAddress");
ds.Tables[0].Columns.Remove("FIsCustomize");
ds.Tables[0].Columns.Remove("FBookRoomNo");
ds.Tables[0].Columns.Remove("FShopID");
ds.Tables[0].Columns.Remove("FTimestamp");
ds.Tables[0].Columns.Remove("FSwitchFlag");
ds.Tables[0].Columns.Remove("FEmpID");
ds.Tables[0].Columns.Remove("ForgEmpID");
ds.Tables[0].Columns.Remove("FIsRevoked");
ds.Tables[0].Columns.Remove("FShopDay");
ds.Tables[0].Columns.Remove("FMorecont");
ds.Tables[0].Columns.Remove("FSendReport");
ds.Tables[0].Columns.Remove("FBillReport");
ds.Tables[0].Columns.Remove("FName");
ds.Tables[0].Columns.Remove("billstate");
ds.Tables[0].Columns["FFeastType"].ColumnName = "酒席类别"; //将字段名改为中文
ds.Tables[0].Columns["FFeastName"].ColumnName = "酒席名称";
ds.Tables[0].Columns["FBookinTime"].ColumnName = "预订时间";
ds.Tables[0].Columns["FActionTime"].ColumnName = "开席时间";
ds.Tables[0].Columns["FTableQty"].ColumnName = "预订席数";
ds.Tables[0].Columns["FReserveQty"].ColumnName = "备用席数";
ds.Tables[0].Columns["FDepositAmt"].ColumnName = "预付订金";
ds.Tables[0].Columns["FCustName"].ColumnName = "客户名称";
ds.Tables[0].Columns["FCustPhone"].ColumnName = "联系电话";
ds.Tables[0].Columns["FNote"].ColumnName = "备注";
ds.Tables[0].Columns["empname"].ColumnName = "操作员";
ds.Tables[0].Columns["orgempname"].ColumnName = "经手人";
ToExcelSheet toexcelsheet = new ToExcelSheet();
string fromfile = DateTime.Now.ToString("yyyyMMddhhMMss") + "酒席预定";
toexcelsheet.DataSet2Sheet(ds, fromfile);
}
#endregion
