public static void SaveToExcel(string fileName, DevExpress.XtraGrid.GridControl gridControl)
{
try
{
using (CommonSaveFileDialog dialog = new CommonSaveFileDialog())
{
dialog.DefaultFileName = fileName + "_" + string.Format("{0:yyMMdd_HHmmss}", DateTime.Now);
dialog.DefaultExtension = "xlsx";
dialog.Filters.Add(new CommonFileDialogFilter("Excel 통합문서", "xlsx"));
if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
{
DevExpress.XtraPrinting.XlsxExportOptionsEx op = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
op.AllowSortingAndFiltering = DevExpress.Utils.DefaultBoolean.False;
gridControl.ExportToXlsx(dialog.FileName, op);
}
else
{
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// BandGridView导出为Excel表格
/// </summary>
/// <param name="bgv">将列分组的表格数据视图</param>
/// <param name="FileName">文件名</param>
/// <param name="SheetName">工作表名</param>
public void BandedGridViewToExcel(DevExpress.XtraGrid.Views.BandedGrid.BandedGridView bgv, string FileName, string SheetName = "")
{
SaveFileDialog sDialog = new SaveFileDialog();
sDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
if (FileName.Trim() != "")
{
sDialog.FileName = FileName;
}
if (sDialog.ShowDialog() == DialogResult.OK)
{
DevExpress.XtraPrinting.XlsxExportOptionsEx op = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
op.ExportType = DevExpress.Export.ExportType.WYSIWYG; //保留导出表格的样式
if (SheetName.Trim() != "")
{
op.SheetName = SheetName;//工作簿名称
}
bgv.AppearancePrint.BandPanel.Font = new Font("微软雅黑", 10);
bgv.AppearancePrint.BandPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
bgv.AppearancePrint.Row.Font = new System.Drawing.Font("微软雅黑", 10);
bgv.AppearancePrint.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
bgv.OptionsPrint.PrintHeader = false; //是否打印行头
bgv.OptionsPrint.AutoWidth = false; //获取或设置输出/导出输出中的列的宽度是否会自动改变，以便视图与页面宽度相匹配。
try
{
bgv.ExportToXlsx(sDialog.FileName, op);
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("导出失败，失败原因：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void button1_Click(object sender, RoutedEventArgs e)
{
DevExpress.XtraPrinting.XlsxExportOptionsEx option = new DevExpress.XtraPrinting.XlsxExportOptionsEx()
{
ExportType = DevExpress.Export.ExportType.WYSIWYG,
TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text,
ShowGridLines = true,
};
(gridControl.View as TableView).ExportToXlsx($"{System.Environment.CurrentDirectory}\\临时文件.xlsx", option);
System.Diagnostics.Process.Start($"{System.Environment.CurrentDirectory}\\临时文件.xlsx");
}
private void GridForm_GridViewSaveToXlsxDataAwareItem_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel File (*.xlsx)|*.Xlsx|All files (*.*)|*.*";
dialog.FilterIndex = 1;
dialog.DefaultExt = "Xlsx";
dialog.AddExtension = true;
DevExpress.XtraPrinting.XlsxExportOptionsEx options = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
options.ExportType = DevExpress.Export.ExportType.DataAware;
if (dialog.ShowDialog() == DialogResult.OK)
{
gridControl.MainView.ExportToXlsx(dialog.FileName, options);
}
}
private void PivotSaveToXlsxMenuItem_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel File (*.xlsx)|*.Xlsx|All files (*.*)|*.*";
dialog.FilterIndex = 0;
dialog.DefaultExt = "Xlsx";
dialog.AddExtension = true;
DevExpress.XtraPrinting.XlsxExportOptionsEx options = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
options.ExportType = DevExpress.Export.ExportType.WYSIWYG;
if (dialog.ShowDialog() == DialogResult.OK)
{
pivotGrid.ExportToXlsx(dialog.FileName, options);
}
}
public static void ExportExcel(GridControl Grc, GridView Grv, string Fin)
{
XtraSaveFileDialog sfd = new XtraSaveFileDialog();
sfd.Title = "Lưu File";
sfd.Filter = "Excel File (*.xlsx) | *.xlsx";
sfd.DefaultExt = ".xlsx";
sfd.FileName = Fin + ".xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
DevExpress.XtraPrinting.XlsxExportOptionsEx op = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
Grv.OptionsPrint.AutoWidth = false;
op.CustomizeCell += op_CustomizeCell;
Grc.ExportToXlsx(sfd.FileName);
if (XtraMessageBox.Show("Xuất excel thành công, mở file ngay!", "Thông Báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
public static void ExportExcell(this PivotGridControl pivot)
{
try
{
string filename = string.Empty;
string value = string.Empty;
if (InputBox("Fayl", "Faylın adını daxil edin:", ref value) == DialogResult.OK)
{
filename = value;
}
else
{
return;
}
if (filename.Trim().Length == 0)
{
return;
}
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.Description = "Qovluq seçin";
if (fbd.ShowDialog() == DialogResult.OK)
{
//SetFileNameFrm sfnfrm = new SetFileNameFrm();
//sfnfrm.ShowDialog();
if (fbd.SelectedPath != string.Empty)
{
filename = fbd.SelectedPath + "\\" + filename + ".xlsx";
if (!File.Exists(filename))
{
if (pivot != null)
{
DevExpress.XtraPrinting.XlsxExportOptionsEx o = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
o.AllowCellMerge = DevExpress.Utils.DefaultBoolean.False;
o.AllowGrouping = DevExpress.Utils.DefaultBoolean.False;
o.ApplyFormattingToEntireColumn = DevExpress.Utils.DefaultBoolean.False;
o.GroupState = DevExpress.Export.GroupState.Default;
o.RawDataMode = false;
pivot.ExportToXlsx(filename, o);
}
DialogResult dr = XtraMessageBox.Show("Faylı indi açmaq istəyirsinizmi?", "",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
if (File.Exists(filename))
{
System.Diagnostics.Process.Start(filename);
}
}
else
{
dr = DialogResult.No;
}
}
else
{
XtraMessageBox.Show("Fayl seçdiyiniz qovluqda mövcuddur");
}
}
}
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
}
private void btnGuiMail_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bạn muốn gửi mail ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
int n = lstDaiLyz.CheckedItems.Count;
if (n == 0)
{
XuLyGiaoDien.Alert("Chưa chọn đại lý tìm", Form_Alert.enmType.Info);
}
else
{
O_CAUHINHSMTP cauHinhSMTPO = cauHinhSMTPD.DuLieu();
O_MAUEMAIL ma = new D_MAUEMAIL().DuLieu()[0];
SmtpClient client = new SmtpClient();
client.Port = cauHinhSMTPO.Port;
client.Host = cauHinhSMTPO.Host;
client.EnableSsl = cauHinhSMTPO.SSL;
client.Timeout = 10000;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential(cauHinhSMTPO.Email, cauHinhSMTPO.Password);
GCG.Visible = true; if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
List <long> a = new List <long>();
string daily = string.Format("{0}", lstDaiLyz.CheckedItems[0]);
a.Add(long.Parse(lstDaiLyz.CheckedItems[0].ToString()));
for (int i = 1; i < n; i++)
{
a.Add(long.Parse(lstDaiLyz.CheckedItems[i].ToString()));
daily += string.Format(",{0}", lstDaiLyz.CheckedItems[i]);
}
lst = new D_HOADON().DuLieu(string.Format("CONVERT(date, NgayThucHien) BETWEEN '{0}' AND '{1}' AND MaHD <> '0' AND ((GiaYeuCau - GiaHeThong) * PhanTram / 100) > 0 AND IDKhachHang in ({2}) ORDER BY IDKhachHang,MaHD,MaCho,GiaHeThong Desc", bdtpTu.DateTime.ToString("yyyyMMdd"), bdtpDen.DateTime.ToString("yyyyMMdd"), daily));
DevExpress.XtraPrinting.XlsxExportOptionsEx opt = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
opt.CustomizeCell += op_CustomizeCell;
opt.SheetName = "Bản CTHD";
opt.ApplyFormattingToEntireColumn = DefaultBoolean.False;
opt.ShowGridLines = false;
bool sendOK = false;
foreach (int b in a)
{
O_DAILY dl = lstDaiLy.Where(w => w.ID.Equals(b)).ToList()[0];
txtMauEmail.HtmlText = ma.NoiDung.Replace("{0}", dl.MaDL).Replace("{1}", XuLyDuLieu.NotVietKey(dl.Ten));
string[] EmailKeToanString = System.Text.RegularExpressions.Regex.Replace(dl.EmailKeToan, @"\t|\n|\r", "|").Replace("||", "|").Split('|');
for (int ii = 0; ii < EmailKeToanString.Count(); ii++)
{
if (EmailKeToanString[ii].Length > 5)
{
List <O_HOADON> lstTam1 = lst.Where(w => w.IDKhachHang.Equals(b)).OrderBy(w => w.MaHD.Replace(" ", string.Empty)).ToList();
List <O_HOADON> lstTam = new List <O_HOADON>();
string newrow = string.Empty;
foreach (O_HOADON hd in lstTam1)
{
if (newrow != hd.MaHD && newrow.Length > 0)
{
lstTam.Add(new O_HOADON());
}
lstTam.Add(hd);
newrow = hd.MaHD;
}
lstTam.Add(new O_HOADON()
{
ID = -1,
GiaHeThong = lstTam.Sum(w => w.GiaHeThong),
GiaYeuCau = lstTam.Sum(w => w.GiaYeuCau),
CL1 = lstTam.Sum(w => w.CL1),
CL2 = lstTam.Sum(w => w.CL2),
});
MailMessage mm = new MailMessage();
mm.From = new MailAddress("*****@*****.**", "Thành Hoàng");
mm.BodyEncoding = UTF8Encoding.UTF8;
mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
mm.IsBodyHtml = true;
RichEditMailMessageExporter exporter = new RichEditMailMessageExporter(txtMauEmail, mm);
exporter.Export();
mm.To.Add(new MailAddress(EmailKeToanString[ii]));
//mm.To.Add(new MailAddress("*****@*****.**"));
XuLyGiaoDien.wait.SetWaitFormDescription("Excel cho: " + dl.Ten);
guiOBindingSource.DataSource = lstTam;
string strFile = @"C:\HoaDon\CT HoaDon " + dl.Ten + ".xlsx";
System.IO.Directory.CreateDirectory(@"C:\HoaDon");
GVG.ExportToXlsx(strFile, opt);
mm.Attachments.Add(new Attachment(strFile));
mm.Subject = "Bảng kê hóa đơn Tháng " + bdtpTu.DateTime.Month + " - " + dl.Ten;
client.Send(mm);
sendOK = true;
mm.Attachments.Dispose();
if (File.Exists(strFile))
{
File.Delete(strFile);
}
}
}
}
GCG.Visible = false;
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
if (sendOK)
{
XuLyGiaoDien.Alert("Gửi mail thành công", Form_Alert.enmType.Success);
}
else
{
XuLyGiaoDien.Alert("Gửi mail không thành công", Form_Alert.enmType.Warning);
}
}
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bạn muốn xuất excel ?", "Câu hỏi", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
int n = lstDaiLyz.CheckedItems.Count;
if (n == 0)
{
XuLyGiaoDien.Alert("Chưa chọn đại lý tìm", Form_Alert.enmType.Info);
}
else
{
GCG.Visible = true;
if (!XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.ShowWaitForm();
}
List <long> a = new List <long>();
string daily = string.Format("{0}", lstDaiLyz.CheckedItems[0]);
a.Add(long.Parse(lstDaiLyz.CheckedItems[0].ToString()));
for (int i = 1; i < n; i++)
{
a.Add(long.Parse(lstDaiLyz.CheckedItems[i].ToString()));
daily += string.Format(",{0}", lstDaiLyz.CheckedItems[i]);
}
lst = new D_HOADON().DuLieu(string.Format("CONVERT(date, NgayThucHien) BETWEEN '{0}' AND '{1}' AND MaHD <> '0' AND ((GiaYeuCau - GiaHeThong) * PhanTram / 100) > 0 AND IDKhachHang in ({2}) ORDER BY IDKhachHang,MaHD,MaCho,GiaHeThong Desc", bdtpTu.DateTime.ToString("yyyyMMdd"), bdtpDen.DateTime.ToString("yyyyMMdd"), daily));
foreach (int b in a)
{
O_DAILY dl = lstDaiLy.Where(w => w.ID.Equals(b)).ToList()[0];
XuLyGiaoDien.wait.SetWaitFormDescription("Excel cho: " + dl.Ten);
List <O_HOADON> lstTam1 = lst.Where(w => w.IDKhachHang.Equals(b)).OrderBy(w => w.MaHD.Replace(" ", string.Empty)).ToList();
List <O_HOADON> lstTam = new List <O_HOADON>();
string newrow = string.Empty;
foreach (O_HOADON hd in lstTam1)
{
if (newrow != hd.MaHD && newrow.Length > 0)
{
lstTam.Add(new O_HOADON());
}
lstTam.Add(hd);
newrow = hd.MaHD;
}
lstTam.Add(new O_HOADON()
{
ID = -1,
GiaHeThong = lstTam.Sum(w => w.GiaHeThong),
GiaYeuCau = lstTam.Sum(w => w.GiaYeuCau),
CL1 = lstTam.Sum(w => w.CL1),
CL2 = lstTam.Sum(w => w.CL2),
});
DevExpress.XtraPrinting.XlsxExportOptionsEx opt = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
opt.CustomizeCell += op_CustomizeCell;
opt.SheetName = "Bản CTHD";
guiOBindingSource.DataSource = lstTam;
opt.ApplyFormattingToEntireColumn = DefaultBoolean.False;
opt.ShowGridLines = false;
string strFile = @"C:\HoaDon\CT HoaDon " + dl.Ten + ".xlsx";
Directory.CreateDirectory(@"C:\HoaDon");
GVG.ExportToXlsx(strFile, opt);
}
GCG.Visible = false;
if (XuLyGiaoDien.wait.IsSplashFormVisible)
{
XuLyGiaoDien.wait.CloseWaitForm();
}
}
}
}
/// <summary>
/// table生成excel xlsx格式
/// </summary>
/// <param name="TableView"></param>
/// <param name="filOutputType">生成的文件名</param>
/// <param name="FilePath">路径</param>
/// <param name="IsStart">是否打开文件</param>
/// <returns></returns>
public static bool CreateTableViewToXlsx(DevExpress.Xpf.Grid.TableView TableView, FileOutputType filOutputType, string FilePath = null, bool IsStart = true)
{
try
{
string FileName = GetFileName(filOutputType);
if (FilePath == null)
{
FilePath = System.Environment.CurrentDirectory + "\\输出文件夹";
}
if (!Directory.Exists(FilePath))
{
Directory.CreateDirectory(FilePath);
}
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel文件 (*.xlsx)|*.xlsx";
dialog.InitialDirectory = FilePath;
dialog.FileName = FileName;
dialog.FilterIndex = 1;
string saveFileName;
if (dialog.ShowDialog() == DialogResult.OK)
{
saveFileName = dialog.FileName;
FileName = Path.GetFileNameWithoutExtension(saveFileName);
}
else
{
return(false);
}
if (File.Exists(saveFileName))
{
FileStream fs = null;
bool inUse = true;
try
{
fs = new FileStream(saveFileName, FileMode.Open, FileAccess.Read, FileShare.None);
inUse = false;
}
catch { }
finally
{
if (fs != null)
{
fs.Close();
}
}
if (inUse)
{
DevExpress.Xpf.Core.DXMessageBox.Show(filOutputType + "正在被使用，请关闭后重新操作。");
return(false);
}
}
DevExpress.XtraPrinting.XlsxExportOptionsEx option = new DevExpress.XtraPrinting.XlsxExportOptionsEx()
{
ExportType = DevExpress.Export.ExportType.WYSIWYG,
TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text,
ShowGridLines = true,
};
TableView.ExportToXlsx(saveFileName, option);
LOGGER.Info($"生成文件{filOutputType}成功");
if (IsStart)
{
//保存多个会打开多个文件夹，应让用户自主选择保存位置
//System.Diagnostics.Process.Start("Explorer.exe", filePath);
System.Diagnostics.Process.Start(saveFileName);
}
return(true);
}
catch (Exception)
{
LOGGER.Error($"生成{filOutputType}文件失败");
//throw new Exception($"生成{FileName}文件失败");
return(false);
}
}