private static void ExportToExcel(DataGridView dtgvExport, string modelNumber)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = modelNumber + "_Result";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < dtgvExport.Rows.Count - 1; i++)
{
for (int j = 0; j < dtgvExport.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void button_EXPORT_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exportare";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("D:\\Rezultat.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Visible = true;
}
private void WriteExcel(Dictionary <string, string> dict, string filename, int col = 2)
{
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
int row = 1;
foreach (var key in dict.Keys)
{
oSheet.Cells[row, 1] = key;
string keyVal;
if (dict.TryGetValue(key, out keyVal))
{
oSheet.Cells[row, col] = keyVal;
}
row++;
}
oWB.SaveAs("C:\\Users\\mahmad\\Desktop\\" + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oXL.Quit();
}
catch
{
MessageBox.Show("Something went wrong");
}
}
private void Button2_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.InitialDirectory = @"C:\'";
saveFileDialog1.Title = "Save excel Files";
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "Text files (*.xlsx)|*.xlsx";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "1";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs(saveFileDialog1.FileName);
}
}
public void createDDetailsReport(List <NodeRecord> nodeRecordsList)
{
try {
//Start Excel and get Application object.
oXL1 = new Microsoft.Office.Interop.Excel.Application();
//oXL1.Visible = true;
//Get a new workbook.
oWB1 = (Microsoft.Office.Interop.Excel._Workbook)(oXL1.Workbooks.Add(""));
oSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)oWB1.ActiveSheet;
oSheet1.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
int rows = 1;
for (int i = 0; i < nodeRecordsList.Count(); i++)
{
oSheet1.get_Range("A" + rows, "B" + rows).Merge();
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].name;
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows++, 1].Font.Size = 15;
for (int j = 0; j < nodeRecordsList[i].record.Count(); j++)
{
oSheet1.Cells[rows, 1].Value2 = nodeRecordsList[i].record[j].status;
if (nodeRecordsList[i].record[j].status.Equals("Online"))
{
oSheet1.Cells[rows, 1].Font.Color = 0x22EE22;
}
else
{
oSheet1.Cells[rows, 1].Font.Color = 0x000000FF;
}
oSheet1.Cells[rows, 1].Font.Bold = true;
oSheet1.Cells[rows, 2].Font.Bold = true;
oSheet1.Cells[rows++, 2].Value2 = nodeRecordsList[i].record[j].timeDate;
}
}
//oRng1 = oSheet.get_Range("A1", "E1");
//oRng1.EntireColumn.AutoFit();
//oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL1.UserControl = false;
oXL1.DisplayAlerts = false;
oWB1.SaveAs(resPath + "Details" + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB1.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Details Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Details Report", buttons);
}
}
public CreateExcel(DataGridView dgv, string excelPath)
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Quit();
}
public static void GenerateSheet(string courseAlias, int sem, string filePath)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
worksheet.Name = "AttendanceSheet";
app.DisplayAlerts = false;
System.Data.DataTable dt = GetStudent(courseAlias, sem);
if (dt.Rows.Count > 0)
{
int count = 1;
int date = 1;
for (int i = 0; i < 33; i++)
{
if (i == 0)
{
worksheet.Cells[1, count] = "RollNo";
}
else if (i == 1)
{
worksheet.Cells[1, count] = "Student Name";
}
else
{
worksheet.Cells[1, count] = date.ToString();
date++;
}
count++;
}
worksheet.Columns.Locked = false;
worksheet.get_Range((object)worksheet.Cells[1, 1], (object)worksheet.Cells[1, 33]).EntireRow.Locked = true;
worksheet.EnableSelection = Excel.XlEnableSelection.xlUnlockedCells;
Missing mv = Missing.Value;
worksheet.Protect(mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv);
count = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[count, 2] = dt.Rows[i][j].ToString();
}
count++;
}
}
workbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}
private void exportExl3(DataGridView dv)
{
try
{
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
int num = 0;
int num2 = 0;
for (num = 0; num < 1; num++)
{
num2 = 0;
while (num2 <= (dv.ColumnCount - 1))
{
worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText;
num2++;
}
}
for (num = 0; num <= (dv.RowCount - 1); num++)
{
for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++)
{
worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value;
}
}
string filename = @"W:\Software\AutoMailMachineBreakdown\Total Incidents Closed (" + TxtTo.Text + ")" + ".xls";
//string filename = string.Empty;
//SaveFileDialog dialog = new SaveFileDialog
//{
// Filter = "Excel files |*.xls|All files (*.*)|*.*",
// FilterIndex = 2,
// RestoreDirectory = true
//};
//if (dialog.ShowDialog() == DialogResult.OK)
//{
//filename = dialog.FileName;
xlApp.DisplayAlerts = false;
workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
xlApp.Quit();
//}
//else
//{
// return;
//}
this.releaseObject(worksheet);
this.releaseObject(workbook);
this.releaseObject(xlApp);
//MessageBox.Show("Exl exported successfully...");
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
private void btnInDoanhSo_Click_1(object sender, EventArgs e)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "BaoCaoDoanhSo";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < dgvDoanhSo.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvDoanhSo.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvDoanhSo.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgvDoanhSo.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Xuất Thông Tin Báo Cáo Doanh Số Thành Công");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void mnutripXuatExcel_Click(object sender, EventArgs e)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
//worksheet.Name = "Exported";
//lúc nào cũng có dòng trắng cuối cùng nên mảng luu bao gom có tieu de = row.cout + 1(tieu de) - 1 (dong trang cuo)
object[,] arr = new object[dtgChiThiSX.Rows.Count, dtgChiThiSX.Columns.Count + 1];
for (int c = 0; c < dtgChiThiSX.Columns.Count; c++)
{
arr[0, c] = dtgChiThiSX.Columns[c].HeaderText;
}
int rowindex = 1;
int colindex = 0;
for (int r = 0; r < dtgChiThiSX.Rows.Count - 1; r++)
{
for (int c = 0; c < dtgChiThiSX.Columns.Count; c++)
{
arr[rowindex, colindex] = dtgChiThiSX.Rows[r].Cells[c].Value.ToString();
colindex++;
}
colindex = 0;
rowindex++;
}
Excel.Range c1 = (Excel.Range)worksheet.Cells[1, 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[1 + dtgChiThiSX.Rows.Count - 1, dtgChiThiSX.Columns.Count + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = arr;
workbook.SaveAs(saveDialog.FileName);
excel.Visible = true;
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//excel.Quit();
workbook = null;
excel = null;
worksheet = null;
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
//worksheet.PageSetup.CenterHeader = lblTraining.Text;
//worksheet.PageSetup.LeftHeader = label1.Text + " " + lblS_date.Text;
//worksheet.PageSetup.RightHeader = label2.Text + " " + lblE_date.Text;
workbook.WebOptions.Encoding = Microsoft.Office.Core.MsoEncoding.msoEncodingUTF8;
app.StandardFont = "Myanmar3";
app.StandardFontSize = 10;
for (int i = 1; i < dgvOfficer.Columns.Count + 1; i++)
{
//worksheet.Range ["A1"].Style
worksheet.Cells[1, i] = dgvOfficer.Columns[i - 1].HeaderText.ToString();
}
for (int i = 0; i <= dgvOfficer.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvOfficer.Columns.Count; j++)
{
if (dgvOfficer.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dgvOfficer.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
object misValue = System.Reflection.Missing.Value;
string caption = "";
if (cboRank_Search.Text.Trim() != "")
{
caption = "အရာရှိစာရင်း(" + cboRank_Search.Text.Trim() + ").xls";
}
else
{
caption = "အရာရှိစာရင်း.xls";
}
workbook.SaveAs(caption, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
app.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button7_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Экспорт завершён");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void ExportToExcel()
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Songs";
int cellRowIndex = 1;
int cellColumnIndex = 1;
for (int i = 0; i < table.Rows.Count - 1; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = table.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = table.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void exportExl(DataGridView dv)
{
try
{
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = xlApp.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
int num = 0;
int num2 = 0;
for (num = 0; num < 1; num++)
{
num2 = 0;
while (num2 <= (dv.ColumnCount - 1))
{
worksheet.Cells[num + 1, num2 + 1] = dv.Columns[num2].HeaderText;
num2++;
}
}
for (num = 0; num <= (dv.RowCount - 1); num++)
{
for (num2 = 0; num2 <= (dv.ColumnCount - 1); num2++)
{
worksheet.Cells[num + 2, num2 + 1] = dv.Rows[num].Cells[num2].Value;
}
}
string filename = string.Empty;
SaveFileDialog dialog = new SaveFileDialog
{
Filter = "Excel files |*.xls|All files (*.*)|*.*",
FilterIndex = 2,
RestoreDirectory = true
};
if (dialog.ShowDialog() == DialogResult.OK)
{
filename = dialog.FileName;
workbook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
xlApp.Quit();
}
else
{
return;
}
this.releaseObject(worksheet);
this.releaseObject(workbook);
this.releaseObject(xlApp);
MessageBox.Show("Exl exported successfully...");
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}
private void xuat_Click(object sender, RoutedEventArgs e)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = excel.ActiveSheet;
excel.Visible = true;
try
{
List <DTO.SoQuy> sq = new List <DTO.SoQuy>();
sq = ((IEnumerable <DTO.SoQuy>) this.statistic.ItemsSource).ToList <DTO.SoQuy>();
var row = 1;
worksheet.Cells[1, "A"] = "Mã phiếu";
worksheet.Cells[1, "B"] = "Thời gian";
worksheet.Cells[1, "C"] = "Loại";
worksheet.Cells[1, "D"] = "Người thu/chi";
worksheet.Cells[1, "E"] = "Giá trị";
foreach (var st in sq)
{
row++;
worksheet.Cells[row, "A"] = st.ID_BILL;
worksheet.Cells[row, "B"] = String.Format("{0:dd/MM/yyyy}", st.DAY_BILL);
worksheet.Cells[row, "C"] = st.TYPE;
worksheet.Cells[row, "D"] = st.NAME;
worksheet.Cells[row, "E"] = st.VALUE;
}
((Excel.Range)worksheet.Columns[1]).AutoFit();
((Excel.Range)worksheet.Columns[2]).AutoFit();
((Excel.Range)worksheet.Columns[3]).AutoFit();
((Excel.Range)worksheet.Columns[4]).AutoFit();
((Excel.Range)worksheet.Columns[5]).AutoFit();
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
Nullable <bool> result = saveDialog.ShowDialog();
if (result == true)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Xuất thành công!");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void closeExcel()
{
oXL.DisplayAlerts = false;
oXL.Visible = false;
oXL.UserControl = true;
//oWB.Save();
oWB.SaveAs(file, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oXL.Quit();
}
public void ExportDataGridview(DataGridView gridView)
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel._Worksheet;
// changing the name of active sheet
worksheet.Name = "data";
// storing header part in Excel
for (int i = 1; i < gridView.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = gridView.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < gridView.Rows.Count - 1; i++)
{
for (int j = 0; j < gridView.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
}
}
string datePatt = @"yyyyMMdd_HH_mm";
string dtString = DateTime.Now.ToString(datePatt);
// save the application
workbook.SaveAs(String.Format("e:\\{0}.xlsx", dtString), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Quit();
}
private void ExportToExcel_clicked(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = @"C:\";
saveFileDialog.Title = "Save text Files";
saveFileDialog.CheckFileExists = true;
saveFileDialog.CheckPathExists = true;
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.Filter = "Text files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileDialog.FilterIndex = 2;
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
String fileName = saveFileDialog.FileName;
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < gribDashboard.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = gribDashboard.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < gribDashboard.Rows.Count - 1; i++)
{
for (int j = 0; j < gribDashboard.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = gribDashboard.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Quit();
}
}
public void DataReport(string filename)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workBook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet workSheet = null;
workSheet = workBook.Sheets["Sheet1"];
workSheet = workBook.ActiveSheet;
workSheet.Name = "Sale Report";
app.Columns.ColumnWidth = 30;
app.DisplayAlerts = false;
System.Globalization.CultureInfo _cultureTHInfo = new System.Globalization.CultureInfo("th-TH");
DateTime dt;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
workSheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 0) // Datetime
{
dt = Convert.ToDateTime(dataGridView1.Rows[i].Cells[j].Value.ToString());
workSheet.Cells[i + 2, j + 1] = dt.ToString("dd MMM yyyy", _cultureTHInfo);
}
else
{
workSheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
}
string lastRow = "E" + Convert.ToString(dataGridView1.Rows.Count);
workSheet.get_Range("A1", lastRow).Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
workSheet.get_Range("A1", "E1").Cells.Font.Bold = true; // Header
workBook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workBook.Close();
app.Quit();
}
public static void ExportExcel(string fileName, string sheetName, DataTable dt)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = sheetName;
//worksheet.Cells.AutoFit();
worksheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells.Font.Name = "Calibri";
worksheet.Cells.Font.Size = 11;
worksheet.Cells.Rows[1].Font.Size = 14;
worksheet.Cells.Rows[1].Font.FontStyle = "Bold";
for (int i = 1; i <= dt.Rows.Count; i++)
{
var dataRow = dt.Rows[i] as DataRow;
for (int j = 0; j < dataRow.ItemArray.Count(); j++)
{
worksheet.Cells[i, j] = dataRow.ItemArray[j].ToString();
}
}
var sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xlsx";
sfd.FileName = fileName;
if (sfd.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(sfd.FileName);
MessageBox.Show("Successful !", "Master Schedule Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Master Schedule Export", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public string SaveExportToExcel(Microsoft.Office.Interop.Excel._Workbook workbook)
{
// Save the application
DateTime time = DateTime.Now; // Use current time
string format = "yyyyMMdd_HHmmss"; // Use this format
// Get file path
string filepathname = string.Empty;
string pathx = System.Web.HttpContext.Current.Server.MapPath("~/workfiles/");
filepathname = pathx + "Export_" + time.ToString(format) + ".xls";
string url = "workfiles/Export_" + time.ToString(format) + ".xls";
workbook.SaveAs(filepathname, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
return(url);
}
//-----------------------------------------------------------------------------------------------------
public static void ExportDatagridToExcel(DataGridView dataGridView1)
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = (Excel._Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//saveFileDialog1.FileName = "sms " + DateTime.Now.ToLongDateString();
saveFileDialog1.Filter = "Text files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
// save the application
workbook.SaveAs(saveFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
// Exit from the application
app.Quit();
}
private void ExportBtn_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure to Export the file ?", "Cofirm Export", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Excel._Application msapp = new Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = msapp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//Can be seen the process of writing in excel
//msapp.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "AccountsDetails";
//Put Header in excel
for (int i = 1; i < dataGridView1.Columns.Count; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
//To put the datagrid data in excel
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count - 1; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
//Pop up save file dailog box
var saveFile = new SaveFileDialog();
saveFile.FileName = "ExportFile";
saveFile.DefaultExt = ".xlsx";
//File foramt
if (saveFile.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFile.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
msapp.Quit();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Predictive Maintenance";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
var saveFileDialogue = new SaveFileDialog();
saveFileDialogue.FileName = "Output Database";
saveFileDialogue.DefaultExt = ".xlsx";
if (saveFileDialogue.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialogue.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
string message = "Saved";
string title = "Notification";
DialogResult result = MessageBox.Show(message, title);
}
private void ExportToExcel()
{
// Creating a Excel object.
int width = Convert.ToInt32(textBox2.Text);
int height = Convert.ToInt32(textBox3.Text);
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "DatGrid4";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 1; i <= height; i++)
{
for (int j = 1; j <= width; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[3].grids[i, j].State;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
Microsoft.Office.Interop.Excel._Worksheet worksheet3 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = workbook.ActiveSheet;
worksheet.Name = "DatGrid3";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 1; i <= height; i++)
{
for (int j = 1; j <= width; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[2].grids[i, j].State;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
Microsoft.Office.Interop.Excel._Worksheet worksheet2 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = workbook.ActiveSheet;
worksheet.Name = "DatGrid2";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 1; i <= height; i++)
{
for (int j = 1; j <= width; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[1].grids[i, j].State;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
Microsoft.Office.Interop.Excel._Worksheet worksheet1 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = workbook.ActiveSheet;
worksheet.Name = "DatGrid1";
cellRowIndex = 1;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 1; i <= height; i++)
{
for (int j = 1; j <= width; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[0].grids[i, j].State;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void ExportToExcel(System.Windows.Forms.DataGridView dgvReports, String title, int tabIndex)
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Open(MyExcel.Template);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Reports";
int rowIndex = 6;
int colIndex = 1;
if (tabIndex == 0)
{
for (int x = -1; x < dgvReports.RowCount; x++)
{
for (int y = 3; y < dgvReports.ColumnCount; y++)
{
if (rowIndex == 6)
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
}
else
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();
if (colIndex == 1)
{
if (dgvReports.Rows[x].Cells[y].Value.ToString() == "1")
{
worksheet.Cells[rowIndex, colIndex] = "Returned";
}
else
{
worksheet.Cells[rowIndex, colIndex] = "Assigned";
}
}
}
colIndex++;
}
colIndex = 1;
rowIndex++;
}
}
else if (tabIndex == 1)
{
for (int x = -1; x < dgvReports.RowCount; x++)
{
for (int y = 2; y < dgvReports.ColumnCount; y++)
{
if (rowIndex == 6)
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
}
else
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();
if (colIndex == 1)
{
if (dgvReports.Rows[x].Cells[y].Value.ToString() == "0")
{
worksheet.Cells[rowIndex, colIndex] = "Throw";
}
else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "1")
{
worksheet.Cells[rowIndex, colIndex] = "Sell";
}
else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "2")
{
worksheet.Cells[rowIndex, colIndex] = "Donate";
}
else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "3")
{
worksheet.Cells[rowIndex, colIndex] = "LOST";
}
}
}
colIndex++;
}
colIndex = 1;
rowIndex++;
}
}
else if (tabIndex == 2)
{
for (int x = -1; x < dgvReports.RowCount; x++)
{
for (int y = 1; y < dgvReports.ColumnCount - 1; y++)
{
if (rowIndex == 6)
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
}
else
{
worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();
}
colIndex++;
}
colIndex = 1;
rowIndex++;
}
}
worksheet.Rows.AutoFit();
worksheet.Columns.AutoFit();
worksheet.Cells[2, "D"] = title;
worksheet.Cells[4, "D"] = DateTime.Now;
worksheet.Cells[3, "D"] = "Exported by " + frmLogin.UserFullName;
worksheet.Cells[4, "F"] = dgvReports.RowCount.ToString() + " records";
//Getting the location and file name of the excel to save from user.
System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
String savePath = saveDialog.FileName + ".xlsx";
workbook.SaveAs(saveDialog.FileName);
System.Windows.Forms.MessageBox.Show(null, "Reports was successfully exported to " + savePath, "Export reports", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
System.Diagnostics.Process.Start(savePath);
}
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void button4_Click(object sender, EventArgs e)
{
Dictionary <string, List <double> > row_folds = new Dictionary <string, List <double> >();
Dictionary <string, List <int> > cpd_descriptor_index = new Dictionary <string, List <int> >();
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel Documents (*.xlsx)|*.xlsx";
saveDialog.FileName = "Correlation_Report.xlsx";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string path = folderBrowserDialog1.SelectedPath;
int index = 0;
foreach (Chart_Correlations chart in charts)
{
Dictionary <string, double> is_3_folds = chart.get_is_3_folds();
foreach (KeyValuePair <string, double> item in is_3_folds)
{
string cpd = item.Key;
double folds = item.Value;
if (row_folds.ContainsKey(cpd))
{
row_folds[cpd].Add(folds);
if (cpd_descriptor_index.ContainsKey(cpd))
{
cpd_descriptor_index[cpd].Add(index);
}
else
{
cpd_descriptor_index[cpd] = new List <int>();
cpd_descriptor_index[cpd].Add(index);
}
}
else
{
row_folds[cpd] = new List <double>();
row_folds[cpd].Add(folds);
if (cpd_descriptor_index.ContainsKey(cpd))
{
cpd_descriptor_index[cpd].Add(index);
}
else
{
cpd_descriptor_index[cpd] = new List <int>();
cpd_descriptor_index[cpd].Add(index);
}
}
}
index++;
}
//DataGridView dataGridView1 = new DataGridView();
f9.dataGridView1.ColumnCount = charts.Count + 1;
int col_index = 0;
foreach (DataGridViewColumn col in f9.dataGridView1.Columns)
{
if (col_index < charts.Count + 1)
{
f9.dataGridView1.Columns[col_index].HeaderText = f8.dataGridView1.Columns[col_index].HeaderText;
}
col_index++;
}
foreach (KeyValuePair <string, List <double> > item in row_folds)
{
var index_row = f9.dataGridView1.Rows.Add(); //(DataGridViewRow)f9.dataGridView1.Rows[0].Clone();
DataGridViewRow current_row = f9.dataGridView1.Rows[index_row];
string cpd = item.Key;
List <double> folds = item.Value;
current_row.Cells[0].Value = cpd;
for (int i = 0; i < folds.Count(); i++)
{
current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Value = folds[i];
if (folds[i] <= fold_number && folds[i] > 1.0 / fold_number)
{
current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Style.BackColor = Color.LightGreen;
}
else
{
current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Style.BackColor = Color.Tomato;
}
}
//f9.dataGridView1.Rows.Add(current_row);
}
//f9.Show();
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < f9.dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < f9.dataGridView1.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = f9.dataGridView1.Columns[j].HeaderText;
if (j == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = f9.dataGridView1.Columns[j].HeaderText + " [Folds]";
}
worksheet.Cells[cellRowIndex, cellColumnIndex].Interior.Color = Color.LightGray;
worksheet.Cells[cellRowIndex, cellColumnIndex].Borders.Weight = 1d;
}
if (j > 0)
{
worksheet.Cells[cellRowIndex + 1, cellColumnIndex] = f9.dataGridView1.Rows[i].Cells[j].Value; //Convert.ToDouble(dataGridViewExport.Rows[i].Cells[j].Value);
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = f9.dataGridView1.Rows[i].Cells[j].Style.BackColor; //worksheet.Cells[cellRowIndex + 1, cellColumnIndex].NumberFormat = "0.00E+00";
if (f9.dataGridView1.Rows[i].Cells[j].Value == null || f9.dataGridView1.Rows[i].Cells[j].Value == DBNull.Value ||
String.IsNullOrWhiteSpace(f9.dataGridView1.Rows[i].Cells[j].Value.ToString()))
{
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = Color.White;
}
}
if (j == 0)
{
worksheet.Cells[cellRowIndex + 1, cellColumnIndex] = f9.dataGridView1.Rows[i].Cells[j].Value;
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = Color.LightGray;
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Borders.Weight = 1d;
}
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
worksheet.UsedRange.Columns.AutoFit();
worksheet.UsedRange.Rows.AutoFit();
}
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Report Generated.");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
}
private void ExportSecondSheet()
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheShortedCurrentVehicleDataSet.shortenlist.Rows.Count;
intColumnNumberOfRecords = TheShortedCurrentVehicleDataSet.shortenlist.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheShortedCurrentVehicleDataSet.shortenlist.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheShortedCurrentVehicleDataSet.shortenlist.Rows[intRowCounter][intColumnCounter].ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Four Thirty Report // Main Window // Export Second Sheet " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
expExportToExcel.IsExpanded = false;
worksheet = workbook.ActiveSheet;
worksheet.Name = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheDepartmentWorkTaskDataSet.worktask.Rows.Count;
intColumnNumberOfRecords = TheDepartmentWorkTaskDataSet.worktask.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDepartmentWorkTaskDataSet.worktask.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDepartmentWorkTaskDataSet.worktask.Rows[intRowCounter][intColumnCounter].ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Sorted Work Task Report // Export To Excel " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void createColorScaleExcel()
{
try {
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Columns["A:F"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
oSheet.get_Range("A1:A2", "B1:B2").Merge();
oSheet.Cells[1, 1].Value2 = "From: " + reportDate[0] + " at " + reportTimePeriod[0] + "\nTo: " + reportDate[1] + " at " + reportTimePeriod[1];
int m = 3;
for (int i = 0; i < nodePathInfoList.Count(); i++)
{
oSheet.Cells[m, 1].Value2 = nodePathInfoList[i].portName + ":";
oSheet.Cells[m, 1].Font.Bold = true;
oSheet.Cells[m, 1].Font.Size = 20;
oSheet.Cells[m, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
oSheet.get_Range("A" + m.ToString(), "B" + m.ToString()).Merge();
// Down to top counter
int z = nodePathInfoList[i].nodePathData.Count() - 1;
for (int j = 0; j < nodePathInfoList[i].nodePathData.Count(); j++)
{
if (j != 0 && j % 6 == 0)
{
m += 4;
}
oSheet.Cells[m + 1, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(1);
oSheet.Cells[m + 2, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(2);
oSheet.Cells[m + 3, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(3);
z--;
// Set all borders for table
oSheet.Cells[m + 1, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 2, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Cells[m + 3, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
// Create a color scale for third row
cfColorScale = (ColorScale)(oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).FormatConditions.AddColorScale(2));
// Min and Max color
cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF; // Red
cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00; // Green
// Set table font size and bold
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Size = 11;
oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Bold = true;
oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Bold = true;
}
m += 6;
}
oRng = oSheet.get_Range("A1", "E1");
oRng.EntireColumn.AutoFit();
oSheet.Columns["A:F"].ColumnWidth = 18;
// oXL.Visible = false;
oXL.UserControl = false;
oXL.DisplayAlerts = false;
oWB.SaveAs(resPath + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
reportLabel.Invoke((MethodInvoker) delegate {
reportLabel.Text = "Report is saved successfully";
});
} catch (Exception e) {
MessageBoxButtons buttons = MessageBoxButtons.OK;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(e.Message, "Reports", buttons);
}
}