public static void ExportGrid2Excel(System.Windows.Forms.Form Form,
Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter ExcelExporter,
Infragistics.Win.UltraWinGrid.UltraGrid Grid)
{
Cursor oldCursor = Form.Cursor;
Form.Cursor = Cursors.WaitCursor;
if (Constant.WaitingForm == null)
{
Constant.WaitingForm = new WaitingForm();
}
Constant.WaitingForm.ShowToUser = true;
Constant.WaitingForm.Show();
Constant.WaitingForm.Update();
try
{
if (!System.IO.Directory.Exists(System.Environment.CurrentDirectory + "\\temp"))
{
System.IO.Directory.CreateDirectory(System.Environment.CurrentDirectory + "\\temp");
}
string StrfileName = string.Format(System.Environment.CurrentDirectory + "\\temp\\" + Form.Text + ".xls");
ExcelExporter.Export(Grid, StrfileName);
ProcessStartInfo p = new ProcessStartInfo(StrfileName);
p.WorkingDirectory = Path.GetDirectoryName(StrfileName);
Process.Start(p);
Form.Cursor = oldCursor;
Constant.WaitingForm.ShowToUser = false;
Constant.WaitingForm.Close();
}
catch (Exception ex)
{
Form.Cursor = oldCursor;
Constant.WaitingForm.ShowToUser = false;
Constant.WaitingForm.Close();
MessageBox.Show(ex.Message);
}
}
public void ExcelExport(Infragistics.Win.UltraWinGrid.UltraGrid Grid, string FileName)
{
try
{
Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter Export = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
SaveFileDialog Save = new SaveFileDialog();
Save.Filter = "Excel File (.xlsx) |*.xlsx|Excel File 97-2003 (.xls)|*.xls";
Save.Title = "Save The Fitness Value Report";
Save.FileName = FileName;
if (Save.ShowDialog() == DialogResult.OK)
{
Export.Export(Grid, Save.FileName);
MessageBox.Show("Excel File was exported successfully!", "Excel File Exportation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ExportData()
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
DateTime FromDate = DateTime.Parse(dtFromDate.Value.ToString());
DateTime ToDate = DateTime.Parse(dtToDate.Value.ToString());
saveFileDialog1.DefaultExt = "txt";
saveFileDialog1.Filter = "ExcelFile |*.xlsx";
saveFileDialog1.AddExtension = true;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.Title = "Bạn cần lưu file ở đâu?";
saveFileDialog1.InitialDirectory = @"C:/";
string datetime = string.Format("Tử ngày {0} đến ngày {1}", FromDate.ToString("dd/MM/yyyy"), ToDate.ToString("dd/MM/yyyy"));
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
var row = Session.CompanyInfo;
string[] lstTitle = { String.Format("{0} - {1}", row.Name, row.Address), "BÁO CÁO DOANH THU THEO NHÂN VIÊN", datetime };
Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
if (Path.GetExtension(saveFileDialog1.FileName) == ".xlsx")
{
workbook.SetCurrentFormat(WorkbookFormat.Excel2007);
}
workbook.Culture = CultureInfo.CurrentCulture;
Infragistics.Documents.Excel.Worksheet SheetExcel = null;
string sGroupSeparator = workbook.Culture.NumberFormat.CurrencyGroupSeparator;
string sDecimalSeparator = workbook.Culture.NumberFormat.CurrencyDecimalSeparator;
List <UltraGridColumn> lstColumn = new List <UltraGridColumn>();
UltraGridColumn clColumn = grdList.DisplayLayout.Bands[0].GetFirstVisibleCol(grdList.ActiveColScrollRegion, true);
while (clColumn != null)
{
lstColumn.Add(clColumn);
clColumn = clColumn.GetRelatedVisibleColumn(VisibleRelation.Next);
}
List <UltraGridColumn> lstGroup = new List <UltraGridColumn>();
foreach (UltraGridColumn gridColumnGroup in grdList.DisplayLayout.Bands[0].SortedColumns)
{
if (gridColumnGroup.IsGroupByColumn)
{
lstGroup.Add(gridColumnGroup);
}
}
SheetExcel = workbook.Worksheets.Add("Bao_cao_doanh_thu");
int iHeaderPosition = lstColumn.Count;
for (int i = 0; i <= lstTitle.Length - 1; i++)
{
string sTitle = lstTitle[i];
{
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Alignment = HorizontalCellAlignment.Center;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].Value = sTitle;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Name = "";
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.BottomBorderStyle = CellBorderLineStyle.None;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.TopBorderStyle = CellBorderLineStyle.None;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.LeftBorderStyle = CellBorderLineStyle.None;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.RightBorderStyle = CellBorderLineStyle.None;
if (i == 0)
{
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Height = 280;
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Alignment = HorizontalCellAlignment.Left;
}
else if (i == 1)
{
SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Height = 380;
}
}
SheetExcel.MergedCellsRegions.Add(i, 0, i, iHeaderPosition - 1);
}
Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter ultraGridExcelExporter = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
ultraGridExcelExporter.BandSpacing = Infragistics.Win.UltraWinGrid.ExcelExport.BandSpacing.None;
ultraGridExcelExporter.Export(grdList, SheetExcel, lstTitle.Length, 0);
for (int i = 0; i < lstGroup.Count - 1; i += 1)
{
SheetExcel.Columns[i].SetWidth(10, WorksheetColumnWidthUnit.Pixel);
}
for (int i = lstGroup.Count; i <= lstGroup.Count + lstColumn.Count - 1; i += 1)
{
SheetExcel.Columns[i].SetWidth(lstColumn[i - lstGroup.Count].Width, WorksheetColumnWidthUnit.Pixel);
if (lstColumn[i - lstGroup.Count].DataType == typeof(DateTime))
{
SheetExcel.Columns[i].CellFormat.FormatString = "dd/MM/yyyy";
}
else if (lstColumn[i - lstGroup.Count].DataType == typeof(double) || lstColumn[i - lstGroup.Count].DataType == typeof(decimal) || lstColumn[i - lstGroup.Count].DataType == typeof(int) || lstColumn[i - lstGroup.Count].DataType == typeof(Int16) || lstColumn[i - lstGroup.Count].DataType == typeof(Int32) || lstColumn[i - lstGroup.Count].DataType == typeof(Int64))
{
string sFormat = lstColumn[i - lstGroup.Count].Format == null ? "" : lstColumn[i - lstGroup.Count].Format;
int iDecimalDigit = 0;
if ((sFormat.StartsWith("N") || sFormat.StartsWith("C")) && int.TryParse(sFormat.Substring(1), out iDecimalDigit))
{
if (iDecimalDigit > 0)
{
SheetExcel.Columns[i].CellFormat.FormatString = string.Format(FormatString, sGroupSeparator, sDecimalSeparator, new string('0', iDecimalDigit));
}
else
{
SheetExcel.Columns[i].CellFormat.FormatString = string.Format(FormatString, sGroupSeparator, string.Empty, string.Empty);
}
}
else
{
SheetExcel.Columns[i].CellFormat.FormatString = "0";
}
}
}
workbook.Save(saveFileDialog1.FileName);
}
else
{
}
saveFileDialog1.Dispose();
saveFileDialog1 = null;
}
