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] = "";
}
}
}
}
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 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 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");
}
}
//填充excel
private void fill_excel(Microsoft.Office.Interop.Excel._Worksheet my)
{
my.Cells[3, 4].Value = "灭菌委托单编号：" + dt_out.Rows[0]["灭菌委托单编号"].ToString();
my.Cells[4, 2].Value = Convert.ToDateTime(dt_out.Rows[0]["辐照产品运回日期"]).ToString("yyyy年MM月dd日");
my.Cells[6, 3].Value = "应是合格辐照商。\n辐照商：" + dt_out.Rows[0]["辐照商"].ToString();
my.Cells[6, 5].Value = dt_out.Rows[0]["辐照供应商检查结果"].ToString();
my.Cells[7, 3].Value = "应是合格运输商。\n运输商：" + dt_out.Rows[0]["检查运输商"].ToString();
my.Cells[7, 5].Value = dt_out.Rows[0]["运输商检查结果"].ToString();
my.Cells[8, 1].Value = String.Format("3. 辐照产品数量:{0}箱\n 计{1}托", dt_out.Rows[0]["辐照产品数量箱"].ToString(), dt_out.Rows[0]["辐照产品数量托"].ToString());
my.Cells[8, 5].Value = dt_out.Rows[0]["辐照产品数量检查结果"].ToString();
my.Cells[9, 5].Value = dt_out.Rows[0]["外包装检查结果"].ToString();
my.Cells[10, 5].Value = dt_out.Rows[0]["标签检查结果"].ToString();
my.Cells[11, 3].Value = String.Format("每批照射产品均应有照射报告，且报告中辐照批号与辐照标签上的批号一致。\n\n报告编号：{0}\n\n辐照批号：{1}", dt_out.Rows[0]["报告编号"].ToString(), dt_out.Rows[0]["辐照批号"].ToString());
my.Cells[11, 5].Value = dt_out.Rows[0]["辐照检查结果"].ToString();
my.Cells[12, 5].Value = "取样时间：" + Convert.ToDateTime(dt_out.Rows[0]["取样时间"]).ToString("yyyy年MM月dd日");
my.Cells[13, 1].Value = "说明：" + dt_out.Rows[0]["说明"].ToString();
if (Convert.ToBoolean(dt_out.Rows[0]["符合要求"]))
{
my.Cells[15, 1].Value = "结论： 辐照产品符合要求，正常入库☑\n不符合要求，按不合格品处理□";
}
else
{
my.Cells[15, 1].Value = "结论： 辐照产品符合要求，正常入库□\n不符合要求，按不合格品处理☑";
}
my.Cells[17, 1].Value = String.Format("验收人：{0} {1} 复核人：{2} {3}",
dt_out.Rows[0]["验收人"].ToString(), Convert.ToDateTime(dt_out.Rows[0]["验收日期"]).ToString("yyyy年MM月dd日"),
dt_out.Rows[0]["审核人"].ToString(), Convert.ToDateTime(dt_out.Rows[0]["审核日期"]).ToString("yyyy年MM月dd日"));
my.Cells[18, 1].Value = String.Format("运输商：{0} 操作人：{1} {2}",
dt_out.Rows[0]["运输商"].ToString(), dt_out.Rows[0]["操作人"].ToString(), Convert.ToDateTime(dt_out.Rows[0]["操作日期"]).ToString("yyyy年MM月dd日"));
}
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 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 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 fill_excel(Microsoft.Office.Interop.Excel._Worksheet my)
{
int ind = 0;
if (dt_prodlist.Rows.Count > 3)
{
//在第7行插入
for (int i = 0; i < dt_prodlist.Rows.Count - 3; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[7, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
ind = dt_prodlist.Rows.Count - 3;
}
my.Cells[3, 1].Value = "指令编号：" + dt_prodinstr.Rows[0]["生产指令编号"].ToString();
my.Cells[3, 3].Value = "生产设备：" + dt_prodinstr.Rows[0]["生产设备"].ToString();
my.Cells[3, 6].Value = Convert.ToDateTime(dt_prodinstr.Rows[0]["计划生产日期"]).ToString("yyyy年MM月dd日");
for (int i = 0; i < dt_prodlist.Rows.Count; i++)
{
my.Cells[6 + i, 1] = i + 1;
my.Cells[6 + i, 2] = dt_prodlist.Rows[i]["清洁前产品代码"].ToString();
my.Cells[6 + i, 3] = dt_prodlist.Rows[i]["清洁前批号"].ToString();
my.Cells[6 + i, 4] = dt_prodlist.Rows[i]["数量卷"].ToString();
my.Cells[6 + i, 5] = dt_prodlist.Rows[i]["数量米"].ToString();
my.Cells[6 + i, 6] = dt_prodlist.Rows[i]["清洁后产品代码"].ToString();
}
my.Cells[10 + ind, 1].Value = "备注：" + dt_prodinstr.Rows[0]["备注"].ToString();
my.Cells[11 + ind, 1].Value = String.Format("编制人：{0} {1} 审批人：{2} {3} 接收人：{4} {5}",
dt_prodinstr.Rows[0]["编制人"].ToString(), Convert.ToDateTime(dt_prodinstr.Rows[0]["编制时间"]).ToString("yyyy年MM月dd日"),
dt_prodinstr.Rows[0]["审批人"].ToString(), Convert.ToDateTime(dt_prodinstr.Rows[0]["审批时间"]).ToString("yyyy年MM月dd日"),
dt_prodinstr.Rows[0]["接收人"].ToString(), Convert.ToDateTime(dt_prodinstr.Rows[0]["接收时间"]).ToString("yyyy年MM月dd日"));
}
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);
}
}
private void getSheet()
{
bool exists = false; //can be a 1 if exists or 0 if doesnt
//check current sheets if one is made for today
foreach (Microsoft.Office.Interop.Excel._Worksheet sheet in oWB.Worksheets)
{
//this is for a brand new file
if (sheet.Name == "Sheet1")
{
sheet.Name = DateTime.Now.ToString("MM.dd.yy");
}
if (sheet.Name == DateTime.Now.ToString("MM.dd.yy"))
{
exists = true;
oSheet = sheet;
}
}
if (exists == false)
{
Excel.Worksheet newWorksheet = oWB.Worksheets.Add();
newWorksheet.Name = DateTime.Now.ToString("MM.dd.yy");
oSheet = newWorksheet;
}
}
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();
}
/// <summary>
/// Выравнивает столбцы
/// </summary>
/// <param name="sheet"></param>
/// <param name="start"></param>
/// <param name="rows"></param>
/// <param name="col"></param>
public void Col(Microsoft.Office.Interop.Excel._Worksheet sheet, string start, int rows, int col)
{
Excel.Range r = sheet.get_Range(start, System.Reflection.Missing.Value);
r = r.get_Resize(rows, col);
r.Columns.AutoFit();
ReleaseOb(r);
}
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;
}
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;
}
}
public void ExportToExcel(DataTable Tbl, string ExcelFilePath)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
//MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
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 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 btShow2_Click(object sender, EventArgs e)
{
//Open file
string fname = "";
OpenFileDialog fopen = new OpenFileDialog();
fopen.Filter = "(All file)|*.*|(Excel file)|*.xlsx";
fopen.ShowDialog();
if (fopen.ShowDialog() == DialogResult.OK)
{
fname = fopen.FileName;
txtPath.Text = fopen.FileName;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
dataGridView3.ColumnCount = colCount;
dataGridView3.RowCount = rowCount;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
//write the value to the Grid
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
dataGridView3.Rows[i - 1].Cells[j - 1].Value = xlRange.Cells[i, j].Value2.ToString();
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
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);
}
}
/// <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);
}
}
}
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;
}
private void GetDataFromExcel()
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fname);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
// dt.Column = colCount;
dataGridView.ColumnCount = colCount;
dataGridView.RowCount = rowCount;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
//write the value to the Grid
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
dataGridView.Rows[i - 1].Cells[j - 1].Value = xlRange.Cells[i, j].Value2.ToString();
}
// Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
//add useful things here!
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
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();
}
}
