示例#1
0
        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();
                }
            }
        }
示例#2
0
        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;
        }
示例#3
0
 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();
 }
示例#4
0
        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);
            }
        }
示例#5
0
 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;
            }
        }
示例#7
0
        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");
            }
        }
示例#8
0
        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);
            }
        }
示例#9
0
 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();
 }
示例#10
0
 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;
            }
        }
示例#14
0
        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;
            }
        }
示例#15
0
        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);
            }
        }
示例#16
0
        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;
            }
        }
示例#17
0
        /// <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);
                }
            }
        }
示例#18
0
//		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();
            }
        }
示例#19
0
        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());
     }
 }
示例#21
0
        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;
            }
        }
示例#22
0
        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;
        }
示例#23
0
        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();
        }
示例#24
0
        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
            {
            }
        }
示例#25
0
        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();
            }
        }
示例#26
0
        //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 != "&nbsp;")
                    {
                        worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text;
                    }
                    else
                    {
                        worksheet.Cells[i + 2, j + 1] = "";
                    }
                }
            }
        }
示例#28
0
        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();
            }
        }
示例#29
0
        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();
        }
示例#30
0
 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");
         }
     }