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;
            }
        }
示例#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 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");
            }
        }
示例#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 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);
            }
        }
示例#6
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();
 }
        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;
            }
        }
示例#10
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;
            }
        }
示例#11
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);
            }
        }
示例#12
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;
            }
        }
示例#13
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());
     }
 }
示例#15
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;
            }
        }
示例#16
0
        private void closeExcel()
        {
            oXL.DisplayAlerts = false;
            oXL.Visible       = false;
            oXL.UserControl   = true;
            //oWB.Save();
            oWB.SaveAs(file, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                       false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            oWB.Close();
            oXL.Quit();
        }
示例#17
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();
        }
示例#18
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();
            }
        }
示例#19
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();
        }
示例#20
0
        public static void ExportExcel(string fileName, string sheetName, DataTable dt)
        {
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = sheetName;

                //worksheet.Cells.AutoFit();
                worksheet.Cells.HorizontalAlignment    = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                worksheet.Cells.Font.Name              = "Calibri";
                worksheet.Cells.Font.Size              = 11;
                worksheet.Cells.Rows[1].Font.Size      = 14;
                worksheet.Cells.Rows[1].Font.FontStyle = "Bold";

                for (int i = 1; i <= dt.Rows.Count; i++)
                {
                    var dataRow = dt.Rows[i] as DataRow;
                    for (int j = 0; j < dataRow.ItemArray.Count(); j++)
                    {
                        worksheet.Cells[i, j] = dataRow.ItemArray[j].ToString();
                    }
                }

                var sfd = new System.Windows.Forms.SaveFileDialog();
                sfd.Filter   = "Excel Documents (*.xls)|*.xlsx";
                sfd.FileName = fileName;
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    workbook.SaveAs(sfd.FileName);
                    MessageBox.Show("Successful !", "Master Schedule Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Master Schedule Export", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
        public string SaveExportToExcel(Microsoft.Office.Interop.Excel._Workbook workbook)
        {
            // Save the application
            DateTime time   = DateTime.Now;        // Use current time
            string   format = "yyyyMMdd_HHmmss";   // Use this format

            // Get file path
            string filepathname = string.Empty;
            string pathx        = System.Web.HttpContext.Current.Server.MapPath("~/workfiles/");

            filepathname = pathx + "Export_" + time.ToString(format) + ".xls";
            string url = "workfiles/Export_" + time.ToString(format) + ".xls";

            workbook.SaveAs(filepathname, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);

            return(url);
        }
示例#22
0
        //-----------------------------------------------------------------------------------------------------
        public static void ExportDatagridToExcel(DataGridView dataGridView1)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            // see the excel sheet behind the program
            app.Visible = true;
            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            worksheet = (Excel._Worksheet)workbook.Sheets["Sheet1"];
            worksheet = (Excel._Worksheet)workbook.ActiveSheet;
            // changing the name of active sheet
            worksheet.Name = "Exported from gridview";
            // storing header part in Excel
            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }
            // storing Each row and column value to excel sheet
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            //saveFileDialog1.FileName = "sms " + DateTime.Now.ToLongDateString();
            saveFileDialog1.Filter = "Text files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                // save the application
                workbook.SaveAs(saveFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            // Exit from the application
            app.Quit();
        }
示例#23
0
        private void ExportBtn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure to Export the file ?", "Cofirm Export", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                Excel._Application msapp = new Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook  workbook  = msapp.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                //Can be seen the process of writing in excel
                //msapp.Visible = true;
                worksheet      = workbook.Sheets["Sheet1"];
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = "AccountsDetails";
                //Put Header in excel
                for (int i = 1; i < dataGridView1.Columns.Count; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }

                //To put the datagrid data in excel
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count - 1; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
                //Pop up save file dailog box
                var saveFile = new SaveFileDialog();
                saveFile.FileName   = "ExportFile";
                saveFile.DefaultExt = ".xlsx";
                //File foramt
                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    workbook.SaveAs(saveFile.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
                msapp.Quit();
            }
        }
示例#24
0
        private void btnSave_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel._Application app       = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;
            worksheet      = workbook.Sheets["Sheet1"];
            worksheet      = workbook.ActiveSheet;
            worksheet.Name = "Predictive Maintenance";

            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

            var saveFileDialogue = new SaveFileDialog();

            saveFileDialogue.FileName   = "Output Database";
            saveFileDialogue.DefaultExt = ".xlsx";
            if (saveFileDialogue.ShowDialog() == DialogResult.OK)
            {
                workbook.SaveAs(saveFileDialogue.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            string       message = "Saved";
            string       title   = "Notification";
            DialogResult result  = MessageBox.Show(message, title);
        }
示例#25
0
        private void ExportToExcel()
        {
            // Creating a Excel object.
            int width  = Convert.ToInt32(textBox2.Text);
            int height = Convert.ToInt32(textBox3.Text);

            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;


            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = "DatGrid4";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                //Loop through each row and read value from each column.
                for (int i = 1; i <= height; i++)
                {
                    for (int j = 1; j <= width; j++)
                    {
                        // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[3].grids[i, j].State;
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                Microsoft.Office.Interop.Excel._Worksheet worksheet3 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                worksheet       = workbook.ActiveSheet;
                worksheet.Name  = "DatGrid3";
                cellRowIndex    = 1;
                cellColumnIndex = 1;
                //Loop through each row and read value from each column.
                for (int i = 1; i <= height; i++)
                {
                    for (int j = 1; j <= width; j++)
                    {
                        // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[2].grids[i, j].State;
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }
                Microsoft.Office.Interop.Excel._Worksheet worksheet2 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                worksheet       = workbook.ActiveSheet;
                worksheet.Name  = "DatGrid2";
                cellRowIndex    = 1;
                cellColumnIndex = 1;
                //Loop through each row and read value from each column.
                for (int i = 1; i <= height; i++)
                {
                    for (int j = 1; j <= width; j++)
                    {
                        // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[1].grids[i, j].State;
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }
                Microsoft.Office.Interop.Excel._Worksheet worksheet1 = excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                worksheet       = workbook.ActiveSheet;
                worksheet.Name  = "DatGrid1";
                cellRowIndex    = 1;
                cellColumnIndex = 1;
                //Loop through each row and read value from each column.
                for (int i = 1; i <= height; i++)
                {
                    for (int j = 1; j <= width; j++)
                    {
                        // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = form_decks[0].grids[i, j].State;
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }



                //Getting the location and file name of the excel to save from user.
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 2;

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Export Successful");
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
示例#26
0
        public void ExportToExcel(System.Windows.Forms.DataGridView dgvReports, String title, int tabIndex)
        {
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Open(MyExcel.Template);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = "Reports";

                int rowIndex = 6;
                int colIndex = 1;

                if (tabIndex == 0)
                {
                    for (int x = -1; x < dgvReports.RowCount; x++)
                    {
                        for (int y = 3; y < dgvReports.ColumnCount; y++)
                        {
                            if (rowIndex == 6)
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
                            }
                            else
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();

                                if (colIndex == 1)
                                {
                                    if (dgvReports.Rows[x].Cells[y].Value.ToString() == "1")
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "Returned";
                                    }
                                    else
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "Assigned";
                                    }
                                }
                            }
                            colIndex++;
                        }
                        colIndex = 1;
                        rowIndex++;
                    }
                }
                else if (tabIndex == 1)
                {
                    for (int x = -1; x < dgvReports.RowCount; x++)
                    {
                        for (int y = 2; y < dgvReports.ColumnCount; y++)
                        {
                            if (rowIndex == 6)
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
                            }
                            else
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();

                                if (colIndex == 1)
                                {
                                    if (dgvReports.Rows[x].Cells[y].Value.ToString() == "0")
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "Throw";
                                    }
                                    else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "1")
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "Sell";
                                    }
                                    else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "2")
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "Donate";
                                    }
                                    else if (dgvReports.Rows[x].Cells[y].Value.ToString() == "3")
                                    {
                                        worksheet.Cells[rowIndex, colIndex] = "LOST";
                                    }
                                }
                            }
                            colIndex++;
                        }
                        colIndex = 1;
                        rowIndex++;
                    }
                }
                else if (tabIndex == 2)
                {
                    for (int x = -1; x < dgvReports.RowCount; x++)
                    {
                        for (int y = 1; y < dgvReports.ColumnCount - 1; y++)
                        {
                            if (rowIndex == 6)
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Columns[y].HeaderText;
                            }
                            else
                            {
                                worksheet.Cells[rowIndex, colIndex] = dgvReports.Rows[x].Cells[y].Value.ToString();
                            }
                            colIndex++;
                        }
                        colIndex = 1;
                        rowIndex++;
                    }
                }



                worksheet.Rows.AutoFit();
                worksheet.Columns.AutoFit();
                worksheet.Cells[2, "D"] = title;
                worksheet.Cells[4, "D"] = DateTime.Now;
                worksheet.Cells[3, "D"] = "Exported by " + frmLogin.UserFullName;
                worksheet.Cells[4, "F"] = dgvReports.RowCount.ToString() + " records";

                //Getting the location and file name of the excel to save from user.
                System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 2;

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    String savePath = saveDialog.FileName + ".xlsx";
                    workbook.SaveAs(saveDialog.FileName);
                    System.Windows.Forms.MessageBox.Show(null, "Reports was successfully exported to " + savePath, "Export reports", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
                    System.Diagnostics.Process.Start(savePath);
                }
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
示例#27
0
        private void button4_Click(object sender, EventArgs e)
        {
            Dictionary <string, List <double> > row_folds            = new Dictionary <string, List <double> >();
            Dictionary <string, List <int> >    cpd_descriptor_index = new Dictionary <string, List <int> >();

            //Getting the location and file name of the excel to save from user.
            SaveFileDialog saveDialog = new SaveFileDialog();

            saveDialog.Filter      = "Excel Documents (*.xlsx)|*.xlsx";
            saveDialog.FileName    = "Correlation_Report.xlsx";
            saveDialog.FilterIndex = 2;

            if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string path = folderBrowserDialog1.SelectedPath;

                int index = 0;
                foreach (Chart_Correlations chart in charts)
                {
                    Dictionary <string, double> is_3_folds = chart.get_is_3_folds();

                    foreach (KeyValuePair <string, double> item in is_3_folds)
                    {
                        string cpd   = item.Key;
                        double folds = item.Value;

                        if (row_folds.ContainsKey(cpd))
                        {
                            row_folds[cpd].Add(folds);

                            if (cpd_descriptor_index.ContainsKey(cpd))
                            {
                                cpd_descriptor_index[cpd].Add(index);
                            }
                            else
                            {
                                cpd_descriptor_index[cpd] = new List <int>();
                                cpd_descriptor_index[cpd].Add(index);
                            }
                        }
                        else
                        {
                            row_folds[cpd] = new List <double>();
                            row_folds[cpd].Add(folds);

                            if (cpd_descriptor_index.ContainsKey(cpd))
                            {
                                cpd_descriptor_index[cpd].Add(index);
                            }
                            else
                            {
                                cpd_descriptor_index[cpd] = new List <int>();
                                cpd_descriptor_index[cpd].Add(index);
                            }
                        }
                    }
                    index++;
                }

                //DataGridView dataGridView1 = new DataGridView();
                f9.dataGridView1.ColumnCount = charts.Count + 1;

                int col_index = 0;
                foreach (DataGridViewColumn col in f9.dataGridView1.Columns)
                {
                    if (col_index < charts.Count + 1)
                    {
                        f9.dataGridView1.Columns[col_index].HeaderText = f8.dataGridView1.Columns[col_index].HeaderText;
                    }
                    col_index++;
                }

                foreach (KeyValuePair <string, List <double> > item in row_folds)
                {
                    var             index_row   = f9.dataGridView1.Rows.Add(); //(DataGridViewRow)f9.dataGridView1.Rows[0].Clone();
                    DataGridViewRow current_row = f9.dataGridView1.Rows[index_row];

                    string        cpd   = item.Key;
                    List <double> folds = item.Value;

                    current_row.Cells[0].Value = cpd;

                    for (int i = 0; i < folds.Count(); i++)
                    {
                        current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Value = folds[i];

                        if (folds[i] <= fold_number && folds[i] > 1.0 / fold_number)
                        {
                            current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Style.BackColor = Color.LightGreen;
                        }
                        else
                        {
                            current_row.Cells[cpd_descriptor_index[cpd][i] + 1].Style.BackColor = Color.Tomato;
                        }
                    }

                    //f9.dataGridView1.Rows.Add(current_row);
                }

                //f9.Show();

                // Creating a Excel object.
                Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

                try
                {
                    worksheet = workbook.ActiveSheet;

                    worksheet.Name = "ExportedFromDatGrid";

                    int cellRowIndex    = 1;
                    int cellColumnIndex = 1;

                    //Loop through each row and read value from each column.
                    for (int i = 0; i < f9.dataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < f9.dataGridView1.Columns.Count; j++)
                        {
                            // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                            if (cellRowIndex == 1)
                            {
                                worksheet.Cells[cellRowIndex, cellColumnIndex] = f9.dataGridView1.Columns[j].HeaderText;
                                if (j == 1)
                                {
                                    worksheet.Cells[cellRowIndex, cellColumnIndex] = f9.dataGridView1.Columns[j].HeaderText + " [Folds]";
                                }
                                worksheet.Cells[cellRowIndex, cellColumnIndex].Interior.Color = Color.LightGray;
                                worksheet.Cells[cellRowIndex, cellColumnIndex].Borders.Weight = 1d;
                            }


                            if (j > 0)
                            {
                                worksheet.Cells[cellRowIndex + 1, cellColumnIndex] = f9.dataGridView1.Rows[i].Cells[j].Value;                          //Convert.ToDouble(dataGridViewExport.Rows[i].Cells[j].Value);
                                worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = f9.dataGridView1.Rows[i].Cells[j].Style.BackColor; //worksheet.Cells[cellRowIndex + 1, cellColumnIndex].NumberFormat = "0.00E+00";
                                if (f9.dataGridView1.Rows[i].Cells[j].Value == null || f9.dataGridView1.Rows[i].Cells[j].Value == DBNull.Value ||
                                    String.IsNullOrWhiteSpace(f9.dataGridView1.Rows[i].Cells[j].Value.ToString()))
                                {
                                    worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = Color.White;
                                }
                            }
                            if (j == 0)
                            {
                                worksheet.Cells[cellRowIndex + 1, cellColumnIndex] = f9.dataGridView1.Rows[i].Cells[j].Value;
                                worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Interior.Color = Color.LightGray;
                                worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Borders.Weight = 1d;
                            }
                            worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                            worksheet.Cells[cellRowIndex + 1, cellColumnIndex].Style.VerticalAlignment   = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


                            cellColumnIndex++;
                        }
                        cellColumnIndex = 1;
                        cellRowIndex++;

                        worksheet.UsedRange.Columns.AutoFit();
                        worksheet.UsedRange.Rows.AutoFit();
                    }

                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Report Generated.");
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    excel.Quit();
                    workbook = null;
                    excel    = null;
                }
            }
        }
示例#28
0
        private void ExportSecondSheet()
        {
            int intRowCounter;
            int intRowNumberOfRecords;
            int intColumnCounter;
            int intColumnNumberOfRecords;

            // Creating a Excel object.
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = "OpenOrders";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                intRowNumberOfRecords    = TheShortedCurrentVehicleDataSet.shortenlist.Rows.Count;
                intColumnNumberOfRecords = TheShortedCurrentVehicleDataSet.shortenlist.Columns.Count;

                for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                {
                    worksheet.Cells[cellRowIndex, cellColumnIndex] = TheShortedCurrentVehicleDataSet.shortenlist.Columns[intColumnCounter].ColumnName;

                    cellColumnIndex++;
                }

                cellRowIndex++;
                cellColumnIndex = 1;

                //Loop through each row and read value from each column.
                for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
                {
                    for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                    {
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = TheShortedCurrentVehicleDataSet.shortenlist.Rows[intRowCounter][intColumnCounter].ToString();

                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                //Getting the location and file name of the excel to save from user.
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 1;

                saveDialog.ShowDialog();

                workbook.SaveAs(saveDialog.FileName);
                MessageBox.Show("Export Successful");
            }
            catch (System.Exception ex)
            {
                TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Four Thirty Report // Main Window // Export Second Sheet " + ex.Message);

                MessageBox.Show(ex.ToString());
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
示例#29
0
        private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
        {
            int intRowCounter;
            int intRowNumberOfRecords;
            int intColumnCounter;
            int intColumnNumberOfRecords;

            // Creating a Excel object.
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                expExportToExcel.IsExpanded = false;

                worksheet = workbook.ActiveSheet;

                worksheet.Name = "OpenOrders";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                intRowNumberOfRecords    = TheDepartmentWorkTaskDataSet.worktask.Rows.Count;
                intColumnNumberOfRecords = TheDepartmentWorkTaskDataSet.worktask.Columns.Count;

                for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                {
                    worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDepartmentWorkTaskDataSet.worktask.Columns[intColumnCounter].ColumnName;

                    cellColumnIndex++;
                }

                cellRowIndex++;
                cellColumnIndex = 1;

                //Loop through each row and read value from each column.
                for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
                {
                    for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                    {
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDepartmentWorkTaskDataSet.worktask.Rows[intRowCounter][intColumnCounter].ToString();

                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                //Getting the location and file name of the excel to save from user.
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 1;

                saveDialog.ShowDialog();

                workbook.SaveAs(saveDialog.FileName);
                MessageBox.Show("Export Successful");
            }
            catch (System.Exception ex)
            {
                TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Sorted Work Task Report // Export To Excel " + ex.Message);

                MessageBox.Show(ex.ToString());
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
示例#30
0
        public void createColorScaleExcel()
        {
            try {
                //Start Excel and get Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
                //oXL.Visible = true;

                //Get a new workbook.
                oWB    = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

                oSheet.Columns["A:F"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

                oSheet.get_Range("A1:A2", "B1:B2").Merge();
                oSheet.Cells[1, 1].Value2 = "From: " + reportDate[0] + " at " + reportTimePeriod[0] + "\nTo: " + reportDate[1] + " at " + reportTimePeriod[1];
                int m = 3;

                for (int i = 0; i < nodePathInfoList.Count(); i++)
                {
                    oSheet.Cells[m, 1].Value2              = nodePathInfoList[i].portName + ":";
                    oSheet.Cells[m, 1].Font.Bold           = true;
                    oSheet.Cells[m, 1].Font.Size           = 20;
                    oSheet.Cells[m, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    oSheet.get_Range("A" + m.ToString(), "B" + m.ToString()).Merge();
                    // Down to top counter
                    int z = nodePathInfoList[i].nodePathData.Count() - 1;

                    for (int j = 0; j < nodePathInfoList[i].nodePathData.Count(); j++)
                    {
                        if (j != 0 && j % 6 == 0)
                        {
                            m += 4;
                        }
                        oSheet.Cells[m + 1, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(1);
                        oSheet.Cells[m + 2, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(2);
                        oSheet.Cells[m + 3, (j % 6) + 1].Value2 = nodePathInfoList[i].nodePathData[z].ElementAt(3);
                        z--;

                        // Set all borders for table
                        oSheet.Cells[m + 1, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
                        oSheet.Cells[m + 2, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();
                        oSheet.Cells[m + 3, (j % 6) + 1].Borders.Color = System.Drawing.Color.Black.ToArgb();

                        // Create a color scale for third row
                        cfColorScale = (ColorScale)(oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).FormatConditions.AddColorScale(2));
                        // Min and Max color
                        cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;   // Red
                        cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00;   // Green

                        // Set table font size and bold
                        oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Size = 11;
                        oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Size = 11;
                        oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Size = 11;

                        oSheet.get_Range("A" + (m + 1).ToString(), "F" + (m + 1).ToString()).Font.Bold = true;
                        oSheet.get_Range("A" + (m + 2).ToString(), "F" + (m + 2).ToString()).Font.Bold = true;
                        oSheet.get_Range("A" + (m + 3).ToString(), "F" + (m + 3).ToString()).Font.Bold = true;
                    }
                    m += 6;
                }

                oRng = oSheet.get_Range("A1", "E1");
                oRng.EntireColumn.AutoFit();

                oSheet.Columns["A:F"].ColumnWidth = 18;

                // oXL.Visible = false;
                oXL.UserControl   = false;
                oXL.DisplayAlerts = false;
                oWB.SaveAs(resPath + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                           false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                oWB.Close();
                reportLabel.Invoke((MethodInvoker) delegate {
                    reportLabel.Text = "Report is saved successfully";
                });
            } catch (Exception e) {
                MessageBoxButtons buttons = MessageBoxButtons.OK;
                DialogResult      result;

                // Displays the MessageBox.
                result = MessageBox.Show(e.Message, "Reports", buttons);
            }
        }