/// <summary>
/// DevExpress通用导出Excel,支持多个控件同时导出在同一个Sheet表
/// eg:ExportToXlsx("",gridControl1,gridControl2);
/// 将gridControl1和gridControl2的数据一同导出到同一张工作表
/// </summary>
/// <param name="title">文件名</param>
/// <param name="panels">控件集</param>
public static void ExportToExcel(string title, GridControl panels)
{
var saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = title;
saveFileDialog.Title = "导出Excel";
saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls";
DialogResult dialogResult = saveFileDialog.ShowDialog();
if (dialogResult == DialogResult.Cancel)
{
return;
}
string FileName = saveFileDialog.FileName;
var ps = new PrintingSystem();
CompositeLink link = new CompositeLink(ps);
ps.Links.Add(link);
link.Links.Add(CreatePrintableLink(panels));
link.Landscape = true;//横向
//判断是否有标题，有则设置
//link.CreateDocument(); //建立文档
try
{
int count = 1;
//在重复名称后加（序号）
while (File.Exists(FileName))
{
if (FileName.Contains(")."))
{
int start = FileName.LastIndexOf("(");
int end = FileName.LastIndexOf(").") - FileName.LastIndexOf("(") + 2;
FileName = FileName.Replace(FileName.Substring(start, end), string.Format("({0}).", count));
}
else
{
FileName = FileName.Replace(".", string.Format("({0}).", count));
}
count++;
}
if (FileName.LastIndexOf(".xlsx") >= FileName.Length - 5)
{
XlsxExportOptions options = new XlsxExportOptions();
link.ExportToXlsx(FileName, options);
}
else
{
XlsExportOptions options = new XlsExportOptions();
link.ExportToXls(FileName, options);
}
if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(FileName);//打开指定路径下的文件
}
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
}
}
public static void ExportToExcel(string title, params IPrintable[] panels)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = title;
saveFileDialog.Title = string.Format("{0} Excel", UnitField.Export);
saveFileDialog.Filter = "Excel (*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
{
return;
}
string text = saveFileDialog.FileName;
PrintingSystem expr_43 = new PrintingSystem();
CompositeLink compositeLink = new CompositeLink(expr_43);
expr_43.Links.Add(compositeLink);
for (int i = 0; i < panels.Length; i++)
{
IPrintable printable = panels[i];
compositeLink.Links.Add(ExportToExcelHelper.CreatePrintableLink(printable));
}
compositeLink.Landscape = true;
try
{
int num = 1;
while (File.Exists(text))
{
if (text.Contains(")."))
{
int startIndex = text.LastIndexOf("(", StringComparison.Ordinal);
int length = text.LastIndexOf(").", StringComparison.Ordinal) - text.LastIndexOf("(", StringComparison.Ordinal) + 2;
text = text.Replace(text.Substring(startIndex, length), string.Format("({0}).", num));
}
else
{
text = text.Replace(".", string.Format("({0}).", num));
}
num++;
}
if (text.LastIndexOf(".xlsx", StringComparison.Ordinal) >= text.Length - 5)
{
XlsxExportOptions options = new XlsxExportOptions();
compositeLink.ExportToXlsx(text, options);
}
else
{
XlsExportOptions options2 = new XlsExportOptions();
compositeLink.ExportToXls(text, options2);
}
if (XtraMessageBox.Show(UnitField.ExportOK, UnitField.SystemMessage, MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
Process.Start(text);
}
}
catch (Exception arg_16F_0)
{
XtraMessageBox.Show(arg_16F_0.Message);
}
}
public void ExportTOExcel(string path)
{
CompositeLink complink = new CompositeLink(new PrintingSystem()); //复合链路
PrintableComponentLink link = new PrintableComponentLink(); //可打印组件链接
link.Component = gridControl1;
complink.Links.Add(link);//Links:获取一个合成基对象的链接的集合;Add:添加指定的DevExpress.XtraPrinting对象到集合
FileInfo fileInfo;
complink.ExportToXls(path, new XlsExportOptions()
{
ExportMode = XlsExportMode.SingleFile, ShowGridLines = true
});
fileInfo = new FileInfo(path);
//fileInfo.IsReadOnly = true;//只读文件
//Close();
}
public void SaveDataToExcel()
{
CompositeLink complink = new CompositeLink(new PrintingSystem()); //复合链路
PrintableComponentLink link = new PrintableComponentLink(); //可打印组件链接
link.Component = gridControl1;
complink.Links.Add(link);//Links:获取一个合成基对象的链接的集合;Add:添加指定的DevExpress.XtraPrinting对象到集合
//complink.CreatePageForEachLink();
string path = "./excel\\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
FileInfo fileInfo;
if (File.Exists(path + excelName + ".xls"))
{
//fileInfo = new FileInfo(path + excelName + ".xls");
//fileInfo.IsReadOnly = false;
//fileInfo.Delete();
}
complink.ExportToXls(path + excelName + ".xls", new XlsExportOptions()
{
ExportMode = XlsExportMode.SingleFile, ShowGridLines = true
});
//complink.ExportToXlsx(path + casenumber + ".xlsx", new XlsxExportOptions() { ExportMode = XlsExportMode.SingleFile, ShowGridLines = true });
fileInfo = new FileInfo(path + excelName + ".xls");
//fileInfo.IsReadOnly = true;//只读文件
XtraMessageBox.Show("保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Close();
}
/// <summary>
/// 匯出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected override void ToExcelButton_Click(object sender, EventArgs e)
{
base.ToExcelButton_Click(sender, e);
if (tabControl1.SelectedIndex == 0)
{
if (gridView1.RowCount <= 0)
{
MessageBox.Show("無內容，請重新查詢", "取消儲存");
return;
}
}
else
{
if (gridView3.RowCount <= 0)
{
MessageBox.Show("無內容，請重新查詢", "取消儲存");
return;
}
}
SaveFileDialog OFD = new SaveFileDialog();
OFD.InitialDirectory = "D:\\";
OFD.RestoreDirectory = true;
OFD.Title = "儲存檔案";
OFD.DefaultExt = "xls";
OFD.Filter = "Microsoft Office Excel 活頁簿 (*.xls)|*.xls";
GridControl[] grids;
if (tabControl1.SelectedIndex == 0)
{
//合併兩個gridcontrol到同一個報表
grids = new GridControl[] { gridControl1 };
}
else
{
grids = new GridControl[] { gridControl3 };
}
OFD.FileName = FileName_OutPutExcel;
DialogResult chooseOutPut = OFD.ShowDialog();
if (chooseOutPut == DialogResult.Cancel)
{
return;
}
//gridview排版
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.OptionsPrint.AutoWidth = false;
gridView1.BestFitColumns();
gridView2.OptionsView.ColumnAutoWidth = false;
gridView2.OptionsPrint.AutoWidth = false;
gridView2.BestFitColumns();
gridView3.OptionsView.ColumnAutoWidth = false;
gridView3.OptionsPrint.AutoWidth = false;
gridView3.BestFitColumns();
gridView1.AppearancePrint.Row.Font = new Font("Arial Unicode MS", 12);
gridView1.AppearancePrint.Row.Options.UseFont = true;
gridView2.AppearancePrint.Row.Font = new Font("Arial Unicode MS", 12);
gridView2.AppearancePrint.Row.Options.UseFont = true;
gridView3.AppearancePrint.Row.Font = new Font("Arial Unicode MS", 12);
gridView3.AppearancePrint.Row.Options.UseFont = true;
PrintingSystem ps = new PrintingSystem();
CompositeLink compositeLink = new CompositeLink();
compositeLink.PrintingSystem = ps;
foreach (GridControl gc in grids)
{
PrintableComponentLink link = new PrintableComponentLink();
link.Component = gc;
compositeLink.Links.Add(link);
}
compositeLink.CreateDocument();
//準備輸出
DevExpress.XtraPrinting.XlsExportOptions XEO = new DevExpress.XtraPrinting.XlsExportOptions();
XEO.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
XEO.ExportMode = XlsExportMode.SingleFile;
XEO.SheetName = FileName_OutPutExcel;
DevExpress.XtraPrinting.PdfExportOptions PEO = new PdfExportOptions();
//PEO.PasswordSecurityOptions.OpenPassword = "******";
#region 檢查檔案是否能寫入
bool IsFileLocked = false;
if (File.Exists(OFD.FileName))
{
FileInfo filepath = new FileInfo(OFD.FileName);
FileStream steam = null;
try
{
steam = filepath.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}
catch (IOException e1)
{
IsFileLocked = true;
}
finally
{
if (steam != null)
{
steam.Close();
}
}
}
#endregion
if (IsFileLocked)
{
MessageBox.Show("檔案被鎖定，請檢查是否有其他程式正在開啟", "匯出失敗");
return;
}
compositeLink.ExportToXls(OFD.FileName, XEO);
//gridView1.ExportToXls(OFD.FileName, XEO);
MessageBox.Show("檔案匯出至 " + OFD.FileName + " 完畢", "匯出成功");
//gridview排版
gridView1.OptionsView.ColumnAutoWidth = true;
gridView1.OptionsPrint.AutoWidth = true;
gridView1.BestFitColumns();
gridView2.OptionsView.ColumnAutoWidth = true;
gridView2.OptionsPrint.AutoWidth = true;
gridView2.BestFitColumns();
gridView3.OptionsView.ColumnAutoWidth = true;
gridView3.OptionsPrint.AutoWidth = true;
gridView3.BestFitColumns();
}