private void UpdateExcel(decimal row, string partno, string model, string pono, string year, string seqno, string sn)
{
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open(AppContext.BaseDirectory + @"\2021.xlsx");
oSheet = String.IsNullOrEmpty("Sheet1") ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets["Sheet1"];
oSheet.Cells[row, 1] = partno;
oSheet.Cells[row, 2] = partno;
oSheet.Cells[row, 3] = partno;
oSheet.Cells[row, 4] = partno;
oSheet.Cells[row, 5] = partno;
oSheet.Cells[row, 6] = partno;
oWB.Save();
MessageBox.Show("Done!");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (oWB != null)
{
oWB.Close();
}
}
}
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 button3_Click_1(object sender, EventArgs e)
{
//todo replace excel
// 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 < dataGridViewct.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridViewct.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridViewct.Rows.Count; i++)
{
for (int j = 0; j < dataGridViewct.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridViewct.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
// workbook.SaveAs("C:\\Users\\lionel\\Desktop\\Test\\output.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);
// Exit from the application
// app.Quit();
}
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 bool DeleteExcelSheet(string pExcelPath, string pSheetName, out string pDeleteSheet)
{
try
{
object objOpt = Missing.Value;
//打开一个Excel应用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook wb = wbs.Open(pExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.EnableAutoRecover = false;
Microsoft.Office.Interop.Excel.Sheets shs = wb.Sheets;
Microsoft.Office.Interop.Excel._Worksheet sh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(pSheetName);
app.DisplayAlerts = false;
sh.Delete();
wb.Save();
pDeleteSheet = string.Empty;
return(true);
}
catch (Exception vErr)
{
pDeleteSheet = vErr.Message;
return(false);
}
finally
{
KillProcess();
}
}
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 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");
}
}
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();
}
private void btnExcel_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;
for (int i = 1; i < gridView1_income.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = gridView1_income.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gridView1_income.Rows.Count - 1; i++)
{
for (int j = 0; j < gridView1_income.Columns.Count; j++)
{
if (gridView1_income.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = gridView1_income.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
}
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;
}
}
/// <summary>
/// expot data from datagridview to excel
/// </summary>
/// <param name="dgv">name of datagridview</param>
/// <param name="filename">excel seve file name</param>
public void excel(DataGridView dgv, string filename)
{
if (dgv.Rows.Count > 0)
{
try
{
// 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 = "School Management System";
// 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("D:\\excel\\" + filename + ".xls", Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
//Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
//Excel.XlSaveConflictResolution.xlUserResolution, true,
//Missing.Value, Missing.Value, Missing.Value);
// workbook.SaveAs("C:\\output.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);
// Exit from the application
// app.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
// public void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
// string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
// string where9, string where10, string User)
// {
// object oMissing = Missing.Value;
// Excel.Application oExcel = new Excel.ApplicationClass();
// Excel.Workbooks oBooks = oExcel.Workbooks;
// Excel._Workbook oBook = null;
//
// System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// try
// {
// oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
// oMissing, oMissing, oMissing, oMissing, oMissing);
// oExcel.Visible = true;
//
// //Cap nhat properties
// SetProperty(oBook, "ConnectionString", ConnectionString);
// SetProperty(oBook, "CoTitle", coTitle);
// SetProperty(oBook, "sFontName", sFontName);
// SetProperty(oBook, "Lang", Lang);
// SetProperty(oBook, "where", where);
// SetProperty(oBook, "where1", where1);
// SetProperty(oBook, "where2", where2);
// SetProperty(oBook, "where3", where3);
// SetProperty(oBook, "where4", where4);
// SetProperty(oBook, "where5", where5);
// SetProperty(oBook, "where6", where6);
// SetProperty(oBook, "where7", where7);
// SetProperty(oBook, "where8", where8);
// SetProperty(oBook, "where9", where9);
// SetProperty(oBook, "where10", where10);
// SetProperty(oBook, "User", User);
//
// try
// {
// RunMacro(oExcel, new object[]{"CreatReport"});
// }
// catch{}
// }
// catch(Exception ex)
// {
// System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
// }
// finally
// {
// System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
// GC.Collect();
// }
// }
public static void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
string where9, string where10, string where11, string User)
{
object oMissing = Missing.Value;
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks oBooks = oExcel.Workbooks;
Microsoft.Office.Interop.Excel._Workbook oBook = null;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Visible = true;
//Cap nhat properties
SetProperty(oBook, "ConnectionString", ConnectionString);
SetProperty(oBook, "CoTitle", coTitle);
SetProperty(oBook, "sFontName", sFontName);
SetProperty(oBook, "Lang", Lang);
SetProperty(oBook, "where", where);
SetProperty(oBook, "where1", where1);
SetProperty(oBook, "where2", where2);
SetProperty(oBook, "where3", where3);
SetProperty(oBook, "where4", where4);
SetProperty(oBook, "where5", where5);
SetProperty(oBook, "where6", where6);
SetProperty(oBook, "where7", where7);
SetProperty(oBook, "where8", where8);
SetProperty(oBook, "where9", where9);
SetProperty(oBook, "where10", where10);
SetProperty(oBook, "where11", where11);
SetProperty(oBook, "User", User);
try
{
RunMacro(oExcel, new object[] { "CreatReport" });
}
catch {}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
GC.Collect();
}
}
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 btnin_Click(object sender, EventArgs e)
{
//Khởi tạo Excel
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//Khởi tạo WorkBook
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//Khởi tạo WorkSheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = true;
worksheet.Cells[2, 2] = "Danh sách hoạt động đoàn thanh niên ";
worksheet.Cells[7, 1] = "STT";
worksheet.Cells[7, 2] = "ID";
worksheet.Cells[7, 3] = "Tên hoạt động";
worksheet.Cells[7, 4] = "Thời gian";
worksheet.Cells[7, 5] = "Mã cán bộ";
worksheet.Cells[7, 6] = "Kết quả";
for (int i = 0; i < dtgrvHdDoan.RowCount; i++)
{
for (int j = 0; j < 5; j++)
{
worksheet.Cells[i + 8, 1] = i + 1;
worksheet.Cells[i + 8, j + 2] = dtgrvHdDoan.Rows[i].Cells[j].Value;
}
}
int HD_Doan = dtgrvHdDoan.RowCount;
//Định dạng trang
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dang cột
worksheet.Range["A1"].ColumnWidth = 8.43;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 27.71;
worksheet.Range["D1"].ColumnWidth = 13;
worksheet.Range["E1"].ColumnWidth = 12.14;
worksheet.Range["F1"].ColumnWidth = 21;
//Định dạng font chữ
worksheet.Range["A1", "F100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "F100"].Font.Size = 14;
worksheet.Range["A2", "F2"].MergeCells = true;
worksheet.Range["A2", "F2"].Font.Bold = true;
worksheet.Range["A2", "F2"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Range["A2", "F2"].Font.Size = 17;
worksheet.Range["A7", "F" + (HD_Doan + 7)].Borders.LineStyle = 1;
}
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();
}
public static void SetProperty(Microsoft.Office.Interop.Excel._Workbook oBook, string Item, string Value)
{
object oCustomProps = oBook.CustomDocumentProperties;
object oBuiltInPro = oBook.BuiltinDocumentProperties;
Type typeCustomProps = oBuiltInPro.GetType();
try
{
typeCustomProps.InvokeMember("Item", BindingFlags.Default | BindingFlags.SetProperty, null, oCustomProps, new object[] { Item, Value });
}
catch
{
}
}
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();
}
}
//private void copyAlltoClipboard()
//{
// dataGridViewSML.RowHeadersVisible = false;
// dataGridViewSML.SelectAll();
// DataObject dataObj = dataGridViewSML.GetClipboardContent();
// if (dataObj != null)
// Clipboard.SetDataObject(dataObj);
//}
private void btExcel_Click(object sender, EventArgs e)
{
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Documents(*.xls) | *.xls";
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;
for (int i = 1; i < dataGridViewSML.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridViewSML.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridViewSML.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridViewSML.Columns.Count; j++)
{
if (dataGridViewSML.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = dataGridViewSML.Rows[i].Cells[j].Value.ToString();
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
// worksheet.Cells[i + 2, j + 1] = AutoSize;
}
}
}
//copyAlltoClipboard();
worksheet.Columns.ColumnWidth = 20;
//worksheet.Columns.FillRight();
app.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
app.ActiveWorkbook.Saved = true;
//app.Quit();
MessageBox.Show("Sucsecful!");
}
else
{
MessageBox.Show("Fail!");
}
}
//*********************************************************************
// Misc Functions
//*********************************************************************
// https://stackoverflow.com/questions/33115067/how-do-i-format-my-export-to-excel-workbook-in-microsoft-office-interop-excel
public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName, int sheetid)
{
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1
worksheet = (Excel.Worksheet)workbook.Worksheets.Add();
// changing the name of active sheet
worksheet.Name = SheetName;
int gridViewCellCount = gridview.Rows[0].Cells.Count;
// string array to hold grid view column names.
string[] columnNames = new string[gridViewCellCount];
// gridview.Rows.Count
int gridViewRowCount = gridview.Rows.Count;
for (int i = 0; i < gridViewCellCount; i++)
{
columnNames[i] = ((System.Web.UI.WebControls.DataControlFieldCell)(gridview.Rows[0].Cells[i])).ContainingField.HeaderText;
}
int iCol = 1;
foreach (var name in columnNames)
{
worksheet.Cells[1, iCol] = name;
iCol++;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < gridViewRowCount; i++)
{
for (int j = 0; j < gridViewCellCount; j++)
{
string cv = gridview.Rows[i].Cells[j].Text;
if (gridview.Rows[i].Cells[j].Text != " ")
{
worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text;
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
}
private void button5_Click(object sender, EventArgs e)
{
var result = MessageBox.Show("This will exit the program and open the log file in Excel. Proceed?", "Application Exit", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
closeExcel();
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.UserControl = true;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Open(file, _missingValue, false, _missingValue, _missingValue, _missingValue, true, _missingValue, _missingValue, true, _missingValue, _missingValue, _missingValue));
this.Close();
}
}
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();
}
private void button1_Click(object sender, System.EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlexcel = null;
Microsoft.Office.Interop.Excel._Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//open existing workbook.
xlexcel.Workbooks.Add("C:\\vehicledet.xlsx");
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Get a new workbook.
xlWorkbook = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value));
xlWorkSheet = (Excel._Worksheet) xlWorkbook.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = true;
xlexcel.UserControl = true;
xlWorkbook.Save();
xlWorkbook.Close();
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
