/// <summary>
        /// �����ݼ�������Ϊexcel
        /// </summary>
        /// <param name="name">����excel������</param>
        /// <param name="ds">�����������ݼ�</param>
        public static void AddExcel(string name, DataTable dt)
        {
            string fileName = name + ".xls";
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            int rowIndex = 1;
            int colIndex = 0;
            excel.Application.Workbooks.Add(true);
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
                }
            }

            excel.Visible = false;
            excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();//��������
        }
    public static void ExportGridViewToExcel(string FileName, GridView gv)
    {
        Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp =
          new Microsoft.Office.Interop.Excel.ApplicationClass();
        try
        {
          ExcelApp.Application.Workbooks.Add(Type.Missing);

          //匯出全部欄位資料
          //for (int i = 1; i < gv.Columns.Count + 1; i++)
          //{
          //  ExcelApp.Cells[1, i] = gv.Columns[i - 1].HeaderText;
          //}
          //for (int i = 0; i < gv.Rows.Count - 1; i++)
          //{
          //  for (int j = 0; j < gv.Columns.Count; j++)
          //  {
          //    ExcelApp.Cells[i + 2, j + 1] = gv.Rows[i].Cells[j].Text.ToString();
          //  }
          //}

          //隱藏第一個欄位資料
          for (int i = 1; i < gv.Columns.Count ; i++)
          {
        ExcelApp.Cells[1, i] = gv.Columns[i].HeaderText;
          }
          for (int i = 0; i < gv.Rows.Count - 1; i++)
          {
        for (int j = 1; j < gv.Columns.Count; j++)
        {
          ExcelApp.Cells[i + 2, j] = gv.Rows[i].Cells[j].Text.ToString().Replace("&nbsp;", "");
        }
          }
          ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
          ExcelApp.ActiveWorkbook.Saved = true;
          ExcelApp.Quit();
        }
        catch (Exception ex)
        {
          throw ex;
        }
        finally
        {
          ExcelApp.Quit();
          System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
          ExcelApp = null;
        }
        GC.Collect();
    }
示例#3
0
        /// <summary>
        /// 把DataTable导出Excel表
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        /// <returns>true为成功,false为失败</returns>
        private static bool _ExportExcel(DataTable dataTable, string fileName)
        {
            bool Flag = false;

            //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            try
            {
                //没有数据的话就不往下执行
                if (dataTable.Rows.Count == 0)
                {
                    return(false);
                }
                excel.Application.Workbooks.Add();
                excel.Visible = false;//要不要显示Excel表
                //判断电脑是否有Excel
                if (excel == null)
                {
                    return(false);
                }
                Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;//
                // 生成字段名称
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
                    ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, i + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                }

                //填充数据
                for (int i = 0; i < dataTable.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        excel.Cells[i + 2, j + 1] = "" + dataTable.Rows[i][j].ToString();
                        ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i + 2, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    }
                }
                SetColumnFit(sheet);//设置自适应宽度
                //保存Excel表
                excel.Save(fileName);
                Flag = true;
            }
            catch
            {
                throw;
            }
            finally
            {
                //设置禁止弹出保存和覆盖的询问提示框
                excel.DisplayAlerts          = true;
                excel.AlertBeforeOverwriting = true;
                excel.Workbooks.Close();
                //确保Excel进程关闭
                excel.Quit();
                excel = null;
                //垃圾回收
                GC.Collect();
            }
            return(Flag);
        }
示例#4
0
        private void buttonX1_Click(object sender, EventArgs e)
        {
            String temp = "";

            temp = "TK_YTQT.xls";

            saveFileDialog1.FileName         = temp.Replace("/", "-");
            saveFileDialog1.Filter           = " Excel (*.xls)|*.xls|Tất cả (*.*)|*.*";
            saveFileDialog1.FilterIndex      = 1;
            saveFileDialog1.RestoreDirectory = true;
            string path = "";

            if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                //Cursor.Current = Cursors.WaitCursor;
                path = saveFileDialog1.FileName;
                Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                ExcelApp.Application.Workbooks.Add(Type.Missing);
                ExcelApp.Columns.ColumnWidth = 30;
                for (int i = 0; i < dataGridViewX2.Rows.Count; i++)
                {
                    DataGridViewRow row = dataGridViewX2.Rows[i];
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        ExcelApp.Cells[i + 1, j + 1] = row.Cells[j].Value.ToString();
                    }
                }

                ExcelApp.ActiveWorkbook.SaveCopyAs(path);
                ExcelApp.ActiveWorkbook.Saved = true;
                ExcelApp.Quit();
                MessageBox.Show("Đã Lưu");
            }
        }
示例#5
0
        /// <summary>
        /// 具体导出的方法
        /// </summary>
        /// <param name="listView">ListView</param>
        /// <param name="strFileName">导出到的文件名</param>
        private void DoExport(string strFileName)
        {
            List <Lecturer> list        = GetAllLecturer();
            int             rowNum      = list.Count;
            int             rowIndex    = 1;
            int             columnIndex = 0;

            if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
            {
                return;
            }
            if (rowNum > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建excel对象,可能您的系统没有安装excel");
                    return;
                }
                xlApp.DefaultFilePath     = "";
                xlApp.DisplayAlerts       = true;
                xlApp.SheetsInNewWorkbook = 1;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                //将ListView的列名导入Excel表第一行
                foreach (ColumnHeader dc in lecturerList.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.Text;
                }

                for (int i = 0; i < list.Count; i++)
                {
                    rowIndex++;
                    Lecturer model = list[i];

                    xlApp.Cells[rowIndex, 1] = Convert.ToString(model.No);
                    xlApp.Cells[rowIndex, 2] = Convert.ToString(model.Name);
                    xlApp.Cells[rowIndex, 3] = Convert.ToString(model.Title);
                    xlApp.Cells[rowIndex, 4] = Convert.ToString(model.Price);
                    xlApp.Cells[rowIndex, 5] = Convert.ToString(model.Phone1);
                    xlApp.Cells[rowIndex, 6] = Convert.ToString(model.Phone2);
                    xlApp.Cells[rowIndex, 7] = Convert.ToString(model.Area);
                    xlApp.Cells[rowIndex, 8] = Convert.ToString(model.From);
                    xlApp.Cells[rowIndex, 9] = Convert.ToString(model.DetialLink);
                }

                xlBook.SaveAs(strFileName, 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);
                xlBook.Close();
                xlApp.Quit();
                xlApp  = null;
                xlBook = null;
                GC.Collect();
                MessageBox.Show("导出完成");
            }
        }
示例#6
0
        /// <summary>
        /// 列数
        /// </summary>
        /// <param name="_filename">文件名(含地址)</param>
        /// <param name="sheetname">工作表名</param>
        /// <returns></returns>
        public int colcount(string _filename, string sheetname) //列数
        {
            Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook         excel_wb;
            excel_wb = myExcelApp.Workbooks.Open(_filename, 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);
            int counts = ((Microsoft.Office.Interop.Excel.Worksheet)excel_wb.Worksheets.get_Item(sheetname)).UsedRange.Columns.Count;

            myExcelApp.Workbooks.Close();
            myExcelApp.Quit();
            myExcelApp = null;
            return(counts);
        }
示例#7
0
        /// <summary>
        /// 获取Excel里面的数据
        /// </summary>
        /// <param name="strPath">文件路径</param>
        /// <param name="col">表示数据有多少列</param>
        /// <param name="row">表示从第几行开始读数据</param>
        /// <returns></returns>
        public static DataTable GetDataFromFile(string strPath, int col, int row)
        {
            DataTable dtRes = MakeTableForImport(col);

            if (!string.IsNullOrEmpty(strPath))
            {
                dtRes.Clear();
                //应将引用的Mircosoft.Office.Intercrop.Excel的引用属性-嵌入式互操作类型 设置为false
                Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                xApp.Visible = false;
                try
                {
                    Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(strPath,
                                                                                         Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                                                         , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                                                         , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
                    int iCount = xSheet.UsedRange.Rows.Count - 1;
                    if (iCount < 1)
                    {
                        return(dtRes);
                    }
                    for (int i = 0; i < iCount; i++)
                    {
                        int     iRowIndex = i + row; // //Excel起始行从1开始计数,首列为标题行,因此数据行计数应先加
                        DataRow dr        = dtRes.NewRow();
                        for (int j = 1; j <= col; j++)
                        {
                            Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xSheet.Cells[iRowIndex, j];
                            if (rng.Text != null)
                            {
                                dr[j - 1] = rng.Text.ToString().Trim();
                            }
                        }
                        dtRes.Rows.Add(dr);
                    }
                }
                catch (Exception ex)
                {
                    SMT.Foundation.Log.Tracer.Debug(" GetDataFromFile获取Excel数据错误:" + ex.ToString());
                }
                finally
                {
                    xApp.Quit();
                }
            }
            return(dtRes);
        }
示例#8
0
        /// <summary>
        /// 数据库转为excel表格
        /// </summary>
        /// <param name="dataTable">数据库数据</param>
        /// <param name="SaveFile">导出的excel文件</param>
        public static void DataSetToExcel(DataTable dataTable, string SaveFile)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook   workBook;
            Microsoft.Office.Interop.Excel._Worksheet  workSheet;
            object misValue = System.Reflection.Missing.Value;

            excel     = new Microsoft.Office.Interop.Excel.ApplicationClass();
            workBook  = excel.Workbooks.Add(misValue);
            workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
            int rowIndex = 1;
            int colIndex = 0;

            //取得标题
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            //取得表格中的数据
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] =
                        row[col.ColumnName].ToString().Trim();
                    //设置表格内容居中对齐

                    //excel.Cells[rowIndex, colIndex].HorizontalAlignment =
                    //Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                }
            }
            excel.Visible = false;
            workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
                            misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                            misValue, misValue, misValue, misValue, misValue);
            dataTable = null;
            workBook.Close(true, misValue, misValue);
            excel.Quit();
            //PublicMethod.Kill(excel);//调用kill当前excel进程
            releaseObject(workSheet);
            releaseObject(workBook);
            releaseObject(excel);
        }
示例#9
0
        private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                var dialog = new SaveFileDialog();
                dialog.Title  = Resources.TextViewForm_ChoiseFileToSave;
                dialog.Filter = "Excel .xls|*.xls";
                //dialog.FileName = "TextView-" + DateTime.Now.ToString("dd-MM-yyyy") + ".xls";
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    ExcelApp.Application.Workbooks.Add(Type.Missing);

                    // Change properties of the Workbook
                    ExcelApp.Columns.ColumnWidth = 20;

                    // Storing header part in Excel
                    for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
                    {
                        ExcelApp.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;
                    }

                    // Storing Each row and column value to excel sheet
                    for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView.Columns.Count; j++)
                        {
                            ExcelApp.Cells[i + 2, j + 1] = dataGridView.Rows[i].Cells[j].Value.ToString().Replace(",", ".");
                        }
                    }

                    ExcelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                    if (ShowConfirmMessage(Resources.Message_ExportSuccess) == DialogResult.OK)
                    {
                        // Open file
                        System.Diagnostics.Process.Start(dialog.FileName);
                    }
                }
            }
            catch (Exception exception)
            {
                ShowErrorMessage(exception.Message);
            }
        }
        private bool XLSConvertToPDF(string sourcePath, string targetPath)
        {
            //  MessageBox.Show(sourcePath+"\n"+targetPath);

            bool result = false;

            Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
            object missing = Type.Missing;

            Microsoft.Office.Interop.Excel.ApplicationClass application = null;
            Microsoft.Office.Interop.Excel.Workbook         workBook    = null;
            try
            {
                application = new Microsoft.Office.Interop.Excel.ApplicationClass();
                object target = targetPath;
                object type   = targetType;
                workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
                                                      missing, missing, missing, missing, missing, missing, missing, missing, missing);

                workBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
                result = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                result = false;
            }
            finally
            {
                if (workBook != null)
                {
                    workBook.Close(true, missing, missing);
                    workBook = null;
                }
                if (application != null)
                {
                    application.Quit();
                    application = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return(result);
        }
示例#11
0
        private void LoadFile2()
        {
            OpenFileDialog pOpen;

            pOpen             = new OpenFileDialog();
            pOpen.Filter      = "Excel files (*.xlsx)|*.xlsx;*.xls";
            pOpen.FilterIndex = 1;
            if (pOpen.ShowDialog() == DialogResult.OK)
            {
                txtFilePath.Text = pOpen.FileName;

                System.DateTime 开始时间1 = System.DateTime.Now;
                Microsoft.Office.Interop.Excel.ApplicationClass oApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                System.DateTime 开始时间2 = System.DateTime.Now;
                System.Diagnostics.Process[] excel进程 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                //立即获取进程创建时间

                int m, killId = 0;
                for (m = 0; m < excel进程.Length; m++)
                {
                    if (开始时间1 < excel进程[m].StartTime && 开始时间2 > excel进程[m].StartTime)
                    {
                        killId = m;
                    }
                }
                //获取最后进程创建时间成功
                Microsoft.Office.Interop.Excel.Workbook oBook = oApp.Workbooks._Open(pOpen.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                comboBox1.Items.Clear();
                comboBox1.Tag = pOpen.FileName;
                foreach (Microsoft.Office.Interop.Excel.Worksheet sSheet in oBook.Sheets)
                {
                    comboBox1.Items.Add(sSheet.Name);
                }

                oBook = null;
                oApp.Quit();
                oApp = null;
                if (excel进程[killId].HasExited == false)
                {
                    excel进程[killId].Kill();
                }
                comboBox1.SelectedIndex = 0;
            }
        }
示例#12
0
 /// <summary>
 /// 数据库转为excel表格
 /// </summary>
 /// <param name="dataTable">数据库数据</param>
 /// <param name="SaveFile">导出的excel文件</param>
 public static void DataSetToExcel(DataTable dataTable, string SaveFile)
 {
     Microsoft.Office.Interop.Excel.Application excel;
         Microsoft.Office.Interop.Excel._Workbook workBook;
         Microsoft.Office.Interop.Excel._Worksheet workSheet;
         object misValue = System.Reflection.Missing.Value;
         excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
         workBook = excel.Workbooks.Add(misValue);
         workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
         int rowIndex = 1;
         int colIndex = 0;
         //取得标题
         foreach (DataColumn col in dataTable.Columns)
         {
             colIndex++;
             excel.Cells[1, colIndex] = col.ColumnName;
         }
         //取得表格中的数据
         foreach (DataRow row in dataTable.Rows)
         {
             rowIndex++;
             colIndex = 0;
             foreach (DataColumn col in dataTable.Columns)
             {
                 colIndex++;
                 excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
                 //设置表格内容居中对齐
                 ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[colIndex, rowIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                 //设置表格自动适应大小
                 workSheet.Cells.Columns.AutoFit();
             }
         }
         excel.Visible = false;
         workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
             misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
             misValue, misValue, misValue, misValue, misValue);
         dataTable = null;
         workBook.Close(true, misValue, misValue);
         excel.Quit();
        PublicMethod.Kill(excel);//调用kill当前excel进程
         releaseObject(workSheet);
         releaseObject(workBook);
         releaseObject(excel);
 }
        /// <summary>
        /// 把Microsoft.Office.Interop.Excel文件转换成PDF格式文件
        /// </summary>
        /// <param name="sourcePath">源文件路径</param>
        /// <param name="targetPath">目标文件路径</param>
        /// <returns>true=转换成功</returns>
        public static bool ExcelToPDF(string sourcePath, string targetPath)
        {
            bool result = false;

            Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
            object missing = Type.Missing;

            Microsoft.Office.Interop.Excel.ApplicationClass application = null;
            Microsoft.Office.Interop.Excel.Workbook         workBook    = null;
            try
            {
                application = new Microsoft.Office.Interop.Excel.ApplicationClass {
                    Visible = false
                };
                workBook = application.Workbooks.Open(sourcePath);
                workBook.SaveAs();
                workBook.ExportAsFixedFormat(targetType, targetPath);
                result = true;
            }
            catch (Exception e)
            {
                result = false;
                throw new Exception(e.Message);
            }
            finally
            {
                if (workBook != null)
                {
                    workBook.Close(true, missing, missing);
                    workBook = null;
                }
                if (application != null)
                {
                    application.Quit();
                    application = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return(result);
        }
示例#14
0
        public void ToXLS(DataGridView dGV, string fileName)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            ExcelApp.Application.Workbooks.Add(Type.Missing);



            // Change properties of the Workbook

            ExcelApp.Columns.ColumnWidth = 20;



            // Storing header part in Excel

            for (int i = 1; i < dGV.Columns.Count + 1; i++)
            {
                ExcelApp.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; i++)
            {
                for (int j = 0; j < dGV.Columns.Count; j++)
                {
                    ExcelApp.Cells[i + 2, j + 1] = dGV.Rows[i].Cells[j].Value.ToString();
                }
            }



            ExcelApp.ActiveWorkbook.SaveCopyAs(fileName);

            ExcelApp.ActiveWorkbook.Saved = true;

            ExcelApp.Quit();
        }
示例#15
0
        /// <summary>
        /// 将数据集导出成为Excel,不需求要Excel模板
        /// </summary>
        /// <param name="name">导出excel的名称</param>
        /// <param name="ds">所导出的数据集</param>
        public static void AddExcel(string name, DataTable dt)
        {
            string fileName = name;

            if (File.Exists(fileName))
            {
                try
                {
                    File.Delete(fileName);
                }
                catch
                {
                    return;
                }
            }
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            int rowIndex = 1;
            int colIndex = 0;

            excel.Application.Workbooks.Add(true);
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
                }
            }
            excel.Visible = false;
            excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();//垃圾回收
        }
示例#16
0
        // M E T O D O S **************************************


        #region Exportar

        // Exportar Clientes
        public void exportarClientes()
        {
            Clientes.frmListaClientes frmListaClientes = new frmListaClientes();


            frmListaClientes.Show();
            frmListaClientes.Hide();

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelAppCli = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelAppCli.Application.Workbooks.Add(Type.Missing);

            SaveFileDialog drCli = new SaveFileDialog();

            drCli.FileName = "Clientes " + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString();
            drCli.Filter   = "Excel files (*.xls)|*.xls";
            drCli.Title    = "CLIENTES";


            if (drCli.ShowDialog() == DialogResult.OK)
            {
                for (int i = 1; i < frmListaClientes.dgvListadoClientes.Columns.Count + 1; i++)
                {
                    ExcelAppCli.Cells[1, i] = frmListaClientes.dgvListadoClientes.Columns[i - 1].HeaderText;
                }

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


                ExcelAppCli.ActiveWorkbook.SaveCopyAs(drCli.FileName);
                ExcelAppCli.ActiveWorkbook.Saved = true;
                ExcelAppCli.Quit();
            }
            int cantCli = frmListaClientes.dgvListadoClientes.Rows.Count;
        }
示例#17
0
        private void btnExcel_Click(object sender, EventArgs e)
        {
            String temp = "DS KHTN dang duoc theo doi";

            saveFileDialog1.FileName         = temp.Replace("/", "-");
            saveFileDialog1.Filter           = "Excel (*.xls)|*.xls|Tất cả (*.*)|*.*";
            saveFileDialog1.FilterIndex      = 1;
            saveFileDialog1.RestoreDirectory = true;
            string path = "";

            if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                Cursor.Current = Cursors.WaitCursor;
                path           = saveFileDialog1.FileName;
                Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                ExcelApp.Application.Workbooks.Add(Type.Missing);
                ExcelApp.Columns.ColumnWidth = 10;

                for (int i = 0; i < dgvDanhsach.Columns.Count; i++)
                {
                    ExcelApp.Cells[1, i + 1] = dgvDanhsach.Columns[i].Name;
                }

                for (int i = 1; i <= dgvDanhsach.Rows.Count; i++)
                {
                    DataGridViewRow row = dgvDanhsach.Rows[i - 1];
                    for (int j = 1; j <= row.Cells.Count; j++)
                    {
                        ExcelApp.Cells[i + 1, j] = row.Cells[j - 1].Value.ToString();
                    }
                }

                ExcelApp.ActiveWorkbook.SaveCopyAs(path);
                ExcelApp.ActiveWorkbook.Saved = true;
                ExcelApp.Quit();
                MessageBox.Show("Đã xuất ra file excel.");
            }
            Cursor.Current = Cursors.Default;
        }
示例#18
0
 //_________________________________________________________________________________
 /// <summary>
 /// 返回工作表名
 /// </summary>
 /// <param name="_filename">文件名(含地址)</param>
 /// <returns>工作表名集</returns>
 public StringCollection countexcel(string _filename) //
 {
     if (System.IO.File.Exists(_filename))
     {
         Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
         Microsoft.Office.Interop.Excel.Workbook         excel_wb;
         excel_wb = myExcelApp.Workbooks.Open(_filename, 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);
         StringCollection a = new StringCollection();
         for (int i = 1; i <= excel_wb.Worksheets.Count; i++)
         {
             a.Add(((Microsoft.Office.Interop.Excel.Worksheet)excel_wb.Worksheets[i]).Name);
         }
         myExcelApp.Workbooks.Close();
         myExcelApp.Quit();
         myExcelApp = null;
         return(a);
     }
     else
     {
         return(null);
     }
 }
        private void button1_Click(object sender, EventArgs e)
        {
            saveFileDialog1.InitialDirectory = "C:";
            saveFileDialog1.Title            = "Save as Excel File";
            saveFileDialog1.FileName         = "";
            saveFileDialog1.Filter           = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xlsx|Excel Files(2010)|*.xlsx|Excel Files(2013)|*.xlsx|Excel Files(2016)|*.xlsx";

            if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                ExcelApp.Application.Workbooks.Add(Type.Missing);

                //Change Properties of the WorkBook
                ExcelApp.Columns.ColumnWidth = 20;

                //Storing the Header Value in Excel
                for (int i = 1; i < dgvCategories.Columns.Count + 1; i++)
                {
                    ExcelApp.Cells[1, i] = dgvCategories.Columns[i - 1].HeaderText;
                }

                //Storing Each row and column value to excel sheet
                for (int i = 0; i < dgvCategories.Rows.Count; i++)
                {
                    for (int j = 0; j < dgvCategories.Columns.Count; j++)
                    {
                        if (dgvCategories.Rows[i].Cells[j].Value != null)
                        {
                            ExcelApp.Cells[i + 2, j + 1] = dgvCategories.Rows[i].Cells[j].Value.ToString();
                        }
                    }
                }

                ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
                ExcelApp.ActiveWorkbook.Saved = true;
                ExcelApp.Quit();
            }
        }
        // Exportar Clientes
        public void exportarClientes()
        {
            Clientes.frmListaClientes frmListaClientes = new frmListaClientes();

            frmListaClientes.Show();
            frmListaClientes.Hide();

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelAppCli = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelAppCli.Application.Workbooks.Add(Type.Missing);

            SaveFileDialog drCli = new SaveFileDialog();
            drCli.FileName = "Clientes " + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString();
            drCli.Filter = "Excel files (*.xls)|*.xls";
            drCli.Title = "CLIENTES";

            if (drCli.ShowDialog() == DialogResult.OK)
            {
                for (int i = 1; i < frmListaClientes.dgvListadoClientes.Columns.Count + 1; i++)
                {
                    ExcelAppCli.Cells[1, i] = frmListaClientes.dgvListadoClientes.Columns[i - 1].HeaderText;
                }

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

                ExcelAppCli.ActiveWorkbook.SaveCopyAs(drCli.FileName);
                ExcelAppCli.ActiveWorkbook.Saved = true;
                ExcelAppCli.Quit();
            }
            int cantCli = frmListaClientes.dgvListadoClientes.Rows.Count;
        }
示例#21
0
        private void Print()
        {
            string fileName = "";

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelAp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks        books   = ExcelAp.Workbooks;
            books.Open(exportPath, 3, false, 3, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                       ";", false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            ExcelAp.ActiveWorkbook.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                            Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            // Cleanup:
            GC.Collect();
            GC.WaitForPendingFinalizers();

            ExcelAp.ActiveWorkbook.Close(false, Type.Missing, Type.Missing);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelAp.ActiveWorkbook);

            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(books);

            ExcelAp.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelAp);
        }
示例#22
0
        //-----------------------------------------------
        //CreateExcelFile
        //-----------------------------------------------
        public static void CreateExcelFile(string excelFilePath, ArrayList ArticlesNos)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = "Article ID";
            for (int i = 0; i < ArticlesNos.Count; i++)
            {
                xlWorkSheet.Cells[i + 2, 1] = ArticlesNos[i];
            }
            xlWorkBook.SaveAs(excelFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            ReleaseObject(xlWorkSheet);
            ReleaseObject(xlWorkBook);
            ReleaseObject(xlApp);
        }
示例#23
0
        /// <summary>
        /// 将DataTable的数据导出显示为报表
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public static void OutputExcel(System.Data.DataTable dt, string strTitle, string filePath)
        {
            var beforeTime = DateTime.Now;

            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook   xBk;
            Microsoft.Office.Interop.Excel._Worksheet  xSt;

            int rowIndex = 4;
            int colIndex = 1;

            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xBk   = excel.Workbooks.Add(true);
            xSt   = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;

            //取得列标题
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[4, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold           = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = 15;//19;//设置为浅黄色,共计有56种
            }


            //取得表格中的数据
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                    if (col.DataType == System.Type.GetType("System.String"))
                    {
                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;    //设置字符型的字段格式为居中对齐
                    }
                    else
                    {
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
            }

            //加载一个合计行
            int rowSum = rowIndex + 1;
            int colSum = 2;

            excel.Cells[rowSum, 2] = "合计";
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //设置选中的部分的颜色
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色,共计有56种

            //取得整个报表的标题
            excel.Cells[2, 2] = strTitle;

            //设置整个报表的标题格式
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight  = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;               //设置左边线加粗
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;               //设置上边线加粗
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight  = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置下边线加粗



            var afterTime = DateTime.Now;

            //显示效果
            //excel.Visible = true;
            //excel.Sheets[0] = "sss";

            // ClearFile(FilePath);
            //string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            //excel.ActiveWorkbook.SaveAs(filePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;

            try
            {
                excel.Save();
            }
            catch (Exception)
            {
                return;
            }

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return;
        }
示例#24
0
        public static bool SetDataToExcel(string pathToExcelFile, string sheetName, DataTable data, string[] intro, string title)
        {
            try
            {

                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();

                Microsoft.Office.Interop.Excel.Workbook wb;
                if (!System.IO.File.Exists(pathToExcelFile))
                {
                    //15/03/2011 Template ?
                    System.IO.File.Copy(SystemHelper.GetConfigValue("appStartupPath") + "\\template\\newWorkbook.xlsx", pathToExcelFile);
                }


                wb = app.Workbooks.Open(pathToExcelFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //Find sheetname
                int index = 1;
                for (index = 1; index <= wb.Sheets.Count; index++)
                {
                    if (((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Name.ToLower().Trim() == sheetName.ToLower().Trim())
                        break;
                }

                if (index > wb.Sheets.Count)
                {
                    wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
                else
                {
                    ((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Activate();
                }



                Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

                activeSheet.Name = sheetName;
                //activeSheet.Cells.FormatConditions = 

                if (!System.IO.File.Exists(pathToExcelFile))
                {
                    //wb.SaveAs(pathToExcelFile, 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);
                    wb.SaveAs(pathToExcelFile, 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);
                }
                else
                {
                    wb.Save();
                }



                // exit excel, still now work so far
                wb.Close(true, Type.Missing, Type.Missing);
                app.Workbooks.Close();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app.Workbooks);
                while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) != 0)
                { };
                //System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Close();

                //System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Kill();


                using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToExcelFile, true))
                {
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0) // the sheet with that name couldn't be found
                    {
                        return false;
                    }

                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);

                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    //sheetData.RemoveAllChildren<Row>();

                    //SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

                    int lastRowIndex = sheetData.ChildElements.Count;
                    int k = 0; //step in intro
                    for (int i = lastRowIndex; i < data.Rows.Count + 4 + lastRowIndex + intro.Length; i++) // +1 for the header label, + 2 for spacing row, + 1 for title
                    {
                        DataRow dataRow = null;

                        if (i > lastRowIndex + 3 + intro.Length)
                        {
                            dataRow = data.Rows[i - 4 - lastRowIndex - intro.Length];
                        }
                        Row aRow = new Row();
                        aRow.RowIndex = (UInt32)i + 1;

                        bool isRowEnd = false;//Fix the excel expand

                        for (int j = 0; j < data.Columns.Count; j++)
                        {
                            if (isRowEnd)
                            {
                                break;//Fix the excel expand
                            }
                            Cell cell = new Cell();
                            cell.DataType = CellValues.InlineString;

                            cell.CellReference = GetAlpha(j + 1) + (i + 1);

                            InlineString inlineString = new InlineString();

                            Text t = new Text();

                            if (i <= lastRowIndex + intro.Length - 1)//Intro
                            {
                                if (j == 0)
                                {
                                    t.Text = intro[k];
                                    k++;
                                    cell.StyleIndex = (UInt32Value)3U;
                                    isRowEnd = true;
                                }
                            }
                            else if (i == lastRowIndex + intro.Length + 1)//Title
                            {
                                if (j == 0)
                                {
                                    t.Text = title;
                                    cell.StyleIndex = (UInt32Value)4U;
                                    isRowEnd = true;
                                }

                            }
                            else if (i == lastRowIndex + 3 + intro.Length)//Header
                            {
                                t.Text = data.Columns[j].ToString();
                                cell.StyleIndex = (UInt32Value)1U;
                            }
                            else
                            {
                                if (i != intro.Length + lastRowIndex && i != intro.Length + lastRowIndex + 2)//Null spacing row
                                    t.Text = dataRow[j].ToString();//Data row
                            }


                            inlineString.AppendChild(t);

                            cell.AppendChild(inlineString);
                            aRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(aRow);
                    }

                    System.Xml.XmlWriter test = System.Xml.XmlWriter.Create("Test.xml");
                    worksheetPart.Worksheet.WriteTo(test);
                    test.Close();

                    worksheetPart.Worksheet.Save();

                    document.WorkbookPart.Workbook.Save();
                    document.Close();
                }

            }
            catch (Exception e)
            {
                SystemHelper.LogEntry(string.Format("Error occurs on method {0} - Message {1}", "GetDataFromExcel", e.Message));
                return false;
            }
            return true;

        }
        /// <summary>
        /// 创建一个空的Excel文件(2003版的Excel)
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string CreateBlankExcel(string path)
        {
            Microsoft.Office.Interop.Excel.Application excelApp;
            Microsoft.Office.Interop.Excel.Workbook excelDoc;

            DateTime excelBeginCreatingTime = DateTime.Now;
            excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            DateTime excelAfterCreatingTime = DateTime.Now;

            if (File.Exists(path))
            {
                File.Delete(path);
            }

            Object nothing = Missing.Value;

            excelDoc = excelApp.Workbooks.Add(nothing);

            Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;

            excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);

            excelDoc.Close(nothing, nothing, nothing);

            excelApp.Quit();

            // 关闭进程
            foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
            {
                DateTime proStartCreatingTime = proc.StartTime;
                if ((proStartCreatingTime >= excelBeginCreatingTime) && (proStartCreatingTime <= excelAfterCreatingTime))
                {
                    proc.Kill();
                }

            }

            return path;
        }
示例#26
0
        public static bool DataGridViewExportToExcel(DataTable mDataTable, String strFileName, ref String strMsg)
        {
            strMsg = "";
            // 创建Excel对象
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            if (xlApp == null)
            {
                strMsg = "Excel无法启动";
                return(false);
            }
            // 创建Excel工作薄
            Microsoft.Office.Interop.Excel.Workbook  xlBook  = xlApp.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];

            Microsoft.Office.Interop.Excel.Range range = null;

            /*
             *          // 设置标题
             *         range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,ts.GridColumnStyles.Count]);
             *         range.MergeCells = true;
             *         xlApp.ActiveCell.FormulaR1C1 = p_ReportName;
             *         xlApp.ActiveCell.Font.Size = 20;
             *         xlApp.ActiveCell.Font.Bold = true;
             *         xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
             */

            // 列索引,行索引,总列数,总行数
            int colIndex = 0;
            int RowIndex = 0;
            int colCount = mDataTable.Columns.Count;
            int RowCount = mDataTable.Rows.Count + 1;

            // 创建缓存数据
            object[,] objData = new object[RowCount + 1, colCount];
            // 获取列标题
            foreach (DataColumn dc in mDataTable.Columns)
            {
                objData[RowIndex, colIndex++] = dc.ColumnName;
            }
            // 获取数据
            for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    objData[RowIndex, colIndex] = mDataTable.Rows[RowIndex - 1][colIndex].ToString();
                }
            }
            // 写入Excel
            //((Excel.Range)xlSheet.Columns["A:A ",System.Reflection.Missing.Value]).ColumnWidth = 16;
            range             = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, colCount]);
            range.Value2      = objData;
            range             = (Microsoft.Office.Interop.Excel.Range)xlSheet.Columns["A:B", System.Type.Missing];
            range.ColumnWidth = 20;
            // 保存
            try
            {
                xlBook.Saved = true;
                xlBook.SaveCopyAs(strFileName);
                MessageBox.Show("数据转存为Excel成功");
            }
            catch (Exception ee)
            {
                strMsg = ee.Message;
                return(false);
            }
            finally
            {
                range   = null;
                xlSheet = null;
                xlBook  = null;
                xlApp.Quit();
                xlApp = null;
                GC.Collect();
            }
            return(true);
        }
示例#27
0
        private void UpdateSitelogin()
        {
            string fullPath  = txtFilePath.Text.Trim();
            string sheetName = comboBox1.Text.Trim() + "$";

            Microsoft.Office.Interop.Excel.ApplicationClass oApp  = null;
            Microsoft.Office.Interop.Excel.Workbook         oBook = null;
            System.Diagnostics.Process[] excel进程 = null;
            int m, killId = 0;

            try
            {
                System.DateTime 开始时间1 = System.DateTime.Now;
                oApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                System.DateTime 开始时间2 = System.DateTime.Now;
                excel进程 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                //立即获取进程创建时间
                for (m = 0; m < excel进程.Length; m++)
                {
                    if (开始时间1 < excel进程[m].StartTime && 开始时间2 > excel进程[m].StartTime)
                    {
                        killId = m;
                    }
                }
                //获取最后进程创建时间成功
                oBook = oApp.Workbooks.Open(fullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                #region 更新Excel
                Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets[sheetName.Replace("$", "")];

                Microsoft.Office.Interop.Excel.Range r;

                int countColumn = oSheet.UsedRange.Columns.Count;
                int countRow    = oSheet.UsedRange.Rows.Count;

                this.progressBar1.Maximum = countRow - 1;
                this.progressBar1.Value   = 0;

                countColumn = 1;
                #region 执行结果
                r        = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[1, countColumn + 1]);
                r.Value2 = "执行结果";

                //找到库里最大值再加2
                int maxnum = GetMaxnum() + 2;
                int num    = maxnum;
                #region SDK方式更新
                for (int i = 2; i < countRow + 1; i++)
                {
                    this.progressBar1.Value++;

                    r = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[i, 1]);//读取第一列数据
                    string cellValue = r.Text.ToString().Trim();
                    r = (Microsoft.Office.Interop.Excel.Range)(oSheet.Cells[i, countColumn + 1]);
                    string resultValue = r.Text.ToString().Trim();
                    num++;
                    if (cellValue == "" || resultValue == "生成失败")
                    {
                        continue;
                    }
                    else
                    {
                        try
                        {
                            EncodingOptions options = null;
                            BarcodeWriter   writer  = null;
                            options = new QrCodeEncodingOptions
                            {
                                DisableECI   = true,
                                CharacterSet = "UTF-8",
                                Width        = 500,
                                Height       = 500,
                                Margin       = 1
                            };

                            writer         = new BarcodeWriter();
                            writer.Format  = BarcodeFormat.QR_CODE;
                            writer.Options = options;

                            Bitmap bitmap = writer.Write(cellValue);

                            System.Drawing.Image Img = bitmap;
                            string msms     = "2017-10-17";
                            string VCardSrc = CardImage.SaveVCardCodeImages(Img, 300, 300, msms, cellValue.Trim());//图片保存路径
                            if (!string.IsNullOrEmpty(VCardSrc))
                            {
                                r.Value2 = VCardSrc + "|" + cellValue;
                                //加入数据库
                                //ADDData(num, cellValue.Trim(), VCardSrc);
                            }
                            else
                            {
                                r.Value2 = "生成失败";
                            }
                        }
                        catch (Exception ex)
                        { r.Value2 = ex.Message; }
                    }
                }
                #endregion



                #endregion

                r = null;
                oBook.Save();
                oBook.Close();
                #endregion

                oBook = null;
                oApp.Quit();
                oApp = null;
                if (excel进程[killId].HasExited == false)
                {
                    excel进程[killId].Kill();
                }

                this.progressBar1.Value = 0;
                MessageBox.Show("生成完毕!");
            }
            catch (Exception ex)
            {
                oBook = null;
                oApp.Quit();
                oApp = null;
                if (excel进程[killId].HasExited == false)
                {
                    excel进程[killId].Kill();
                }

                MessageBox.Show(ex.Message);
            }
            finally
            {
                this.txtFilePath.Enabled = true;
                this.comboBox1.Enabled   = true;
                this.button1.Enabled     = true;
            }
        }
示例#28
0
        private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                var dialog = new SaveFileDialog();
                dialog.Title = Resources.TextViewForm_ChoiseFileToSave;
                dialog.Filter = "Excel .xls|*.xls";
                //dialog.FileName = "TextView-" + DateTime.Now.ToString("dd-MM-yyyy") + ".xls";
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    ExcelApp.Application.Workbooks.Add(Type.Missing);

                    // Change properties of the Workbook
                    ExcelApp.Columns.ColumnWidth = 20;

                    // Storing header part in Excel
                    for (int i = 1; i < dataGridView.Columns.Count + 1; i++)
                    {
                        ExcelApp.Cells[1, i] = dataGridView.Columns[i - 1].HeaderText;
                    }

                    // Storing Each row and column value to excel sheet
                    for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView.Columns.Count; j++)
                        {
                            ExcelApp.Cells[i + 2, j + 1] = dataGridView.Rows[i].Cells[j].Value.ToString().Replace(",", ".");
                        }
                    }

                    ExcelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                    if (ShowConfirmMessage(Resources.Message_ExportSuccess) == DialogResult.OK)
                    {
                        // Open file
                        System.Diagnostics.Process.Start(dialog.FileName);
                    }
                }

            }
            catch (Exception exception)
            {
                ShowErrorMessage(exception.Message);
            }
        }
示例#29
0
        /// <summary>
        /// 导入文本
        /// </summary>
        private void ImportText(string fileName)
        {
            Microsoft.Office.Interop.Excel.Application xApp   = null;
            Microsoft.Office.Interop.Excel.Workbook    xBook  = null;
            Microsoft.Office.Interop.Excel.Worksheet   xSheet = null;

            // 显示进度条
            this.ResetProcessBar(this.fileList.Items.Count);

            try
            {
                StringBuilder failFiles = new StringBuilder();

                // 创建Application对象
                xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

                // 得到WorkBook对象, 打开已有的文件
                xBook = xApp.Workbooks._Open(
                    fileName,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                for (int i = xBook.Sheets.Count; i >= 1; i--)
                {
                    // 取得相应的Sheet
                    xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i];
                    int    sheetIndex = -1;
                    string sheetName  = string.Empty;
                    for (int j = 0; j < this.fileList.Items.Count; j++)
                    {
                        sheetName = Util.GetShortFileName(this.textFiles[j].File);
                        if (xSheet.Name.IndexOf(sheetName) >= 0)
                        {
                            if (xSheet.Name.IndexOf("main.dol") >= 0)
                            {
                                if ((xSheet.Name == "main.dol" && string.IsNullOrEmpty(this.textFiles[j].SubIndex)) ||
                                    (!string.IsNullOrEmpty(this.textFiles[j].SubIndex) && xSheet.Name.IndexOf(this.textFiles[j].SubIndex) >= 0))
                                {
                                    sheetIndex = j;
                                    break;
                                }
                            }
                            else
                            {
                                sheetIndex = j;
                                break;
                            }
                        }
                    }

                    if (sheetIndex > -1)
                    {
                        // 更新当前文本
                        this.fileList.SelectedIndex = sheetIndex;

                        // 取得当前Sheet的中文文本
                        int           lineNum  = 1;
                        int           blankNum = 0;
                        StringBuilder sb       = new StringBuilder();
                        while (blankNum < 4)
                        {
                            string cellValue = xSheet.get_Range("G" + lineNum, Missing.Value).Value2 as string;
                            sb.Append(cellValue).Append("\n");

                            if (string.IsNullOrEmpty(cellValue))
                            {
                                blankNum++;
                            }
                            else
                            {
                                blankNum = 0;
                            }

                            lineNum++;
                        }

                        sb = sb.Replace("\n\n\n\n\n", "\n");

                        this.txtCn.Text = sb.ToString();

                        // 保存
                        if (!this.Save())
                        {
                            //return;
                            throw new Exception("有文件长度检查失败");
                            //failFiles.Append("\n").Append(sheetName);
                        }
                    }

                    // 更新进度条
                    this.ProcessBarStep();
                }

                // 隐藏进度条
                this.CloseProcessBar();

                // 显示保存完成信息
                if (failFiles.Length == 0)
                {
                    MessageBox.Show("完全成功导入!");
                }
                else
                {
                    MessageBox.Show("导入完成,下面文件失败" + failFiles.ToString());
                }

                // 重新读取字库信息
                this.ReadFontChar();
            }
            catch (Exception me)
            {
                MessageBox.Show(this.baseFile + "\n" + me.Message);
            }
            finally
            {
                // 隐藏进度条
                this.CloseProcessBar();

                // 清空各种对象
                xSheet = null;
                xBook  = null;
                if (xApp != null)
                {
                    xApp.Quit();
                    xApp = null;
                }
            }
        }
示例#30
0
        public DataTable Excel2DataTable(string FileName)
        {
            //创建Application对象
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            xApp.Visible = false;
            //Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
            //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
            Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(FileName,

            Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
            //指定要操作的Sheet,两种方式:

            Microsoft.Office.Interop.Excel.Worksheet xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];

            Microsoft.Office.Interop.Excel.Range range = xSheetSample.UsedRange;
            DataTable dt = new DataTable();
            for (int j = 1; j <= range.Columns.Count; j++)
            {
                DataColumn dc = new DataColumn(((Microsoft.Office.Interop.Excel.Range)range.Cells[1, j]).Value2.ToString());
                dt.Columns.Add(dc);

            }
            for (int i = 2; i <= range.Rows.Count; i++)
            {
                DataRow tmprow = dt.NewRow();
                for (int j = 1; j <= range.Columns.Count; j++)
                {
                    tmprow[j - 1] = ((Microsoft.Office.Interop.Excel.Range)range.Cells[1, j]).Value2;
                }
                dt.Rows.Add(tmprow);
            }

            xSheetSample = null;
            xBook = null;
            xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
            xApp = null;
            GC.Collect();
            KillProcess("EXCEL");
            System.Threading.Thread.Sleep(1000 * 3); ;
            return dt;
        }
        static void ExcelRefresh(string Filename, string targetServer, string loadBalance)
        {
            object NullValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            try
            {
                excelApp.DisplayAlerts = false;
                excelApp.AskToUpdateLinks = false;
                excelApp.Visible = true;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, false, NullValue, NullValue,
                   NullValue, true, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);

                if (loadBalance.Equals("Load Balance"))
                {
                    for (int i = 1; i <= Workbook.Connections.Count; i++)
                    {
                        var con = Workbook.Connections.Item(i);
                        var oleDBCon = con.OLEDBConnection;
                        var conStr = (string)oleDBCon.Connection;
                        var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source={0};", targetServer));
                        con.OLEDBConnection.Connection = newConStr;
                    }
                }

                Workbook.RefreshAll();

                /*
                if (loadBalance.Equals("Load Balance"))
                {
                    for (int i = 1; i <= Workbook.Connections.Count; i++)
                    {
                        var con = Workbook.Connections.Item(i);
                        var oleDBCon = con.OLEDBConnection;
                        var conStr = (string)oleDBCon.Connection;
                        var newConStr = Regex.Replace(conStr, @"Data Source=.*?;", String.Format(@"Data Source=tratab.tyson.com;"));
                        con.OLEDBConnection.Connection = newConStr;
                    }
                }
                 */

                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(Workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Workbook);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);

                //Workbook = null;
            }
            catch (Exception ex)
            {

                System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.Message,
                                       System.Diagnostics.EventLogEntryType.Warning);

                System.Diagnostics.EventLog.WriteEntry("RefreshExcelReport", ex.StackTrace,
                                       System.Diagnostics.EventLogEntryType.Warning);

                throw ex;
            }
            finally
            {
                excelApp = null;
            }

            //excelApp.Quit();
        }
示例#32
0
        /// <summary>
        /// 导出文本
        /// </summary>
        private void ExoprtText()
        {
            Microsoft.Office.Interop.Excel.Application xApp   = null;
            Microsoft.Office.Interop.Excel.Workbook    xBook  = null;
            Microsoft.Office.Interop.Excel.Worksheet   xSheet = null;

            // 设定保存的文件名
            string fileName = @"E:\My\Hanhua\testFile\bioCv\BioCvTextNgc.xls";

            //string fileName = @"D:\game\iso\wii\生化危机维罗妮卡汉化\Bio0Text_" + this.exportName + ".xls";

            // 先删除原来的文件
            File.Delete(fileName);

            // 显示进度条
            this.ResetProcessBar(this.fileList.Items.Count);

            try
            {
                // 创建Application对象
                xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //xApp.Visible = true;

                // 追加一个WorkBook
                xBook = xApp.Workbooks.Add(Missing.Value);

                for (int j = 0; j < this.fileList.Items.Count; j++)
                {
                    // 追加一个Sheet
                    FilePosInfo filePosInfo = this.textFiles[j];

                    // 更新当前文本
                    this.fileList.SelectedIndex = j;

                    // 取得日文、中文文本
                    string jpText = this.txtJp.Text;
                    string cnText = this.txtCn.Text;

                    string sheetName     = Util.GetShortFileName(filePosInfo.File);
                    int    sameNameCount = 0;
                    for (int i = 0; i < j; i++)
                    {
                        if (Util.GetShortFileName(this.textFiles[i].File).IndexOf(sheetName) >= 0)
                        {
                            sameNameCount++;
                        }
                    }

                    xSheet      = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    xSheet.Name = sheetName + (sameNameCount > 0 ? "_" + sameNameCount.ToString().PadLeft(2, '0') : string.Empty);

                    // 将每行文本保存到Sheet中
                    string[] jpTexts = jpText.Split('\n');
                    string[] cnTexts = cnText.Split('\n');

                    for (int i = 0; i < jpTexts.Length; i++)
                    {
                        // 写入日文文本
                        Microsoft.Office.Interop.Excel.Range rngJp = xSheet.get_Range("A" + (i + 1), Missing.Value);
                        rngJp.Value2 = jpTexts[i];
                    }
                    for (int i = 0; i < cnTexts.Length; i++)
                    {
                        // 写入中文文本
                        Microsoft.Office.Interop.Excel.Range rngCn = xSheet.get_Range("G" + (i + 1), Missing.Value);
                        rngCn.Value2 = cnTexts[i];
                    }

                    // 更新进度条
                    this.ProcessBarStep();
                }

                // 保存
                xSheet.SaveAs(
                    fileName,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                // 隐藏进度条
                this.CloseProcessBar();

                // 显示保存完成信息
                MessageBox.Show("导出完成!");
            }
            catch (Exception me)
            {
                MessageBox.Show(this.baseFile + "\n" + me.Message);
            }
            finally
            {
                // 隐藏进度条
                this.CloseProcessBar();

                // 清空各种对象
                xSheet = null;
                xBook  = null;
                if (xApp != null)
                {
                    xApp.Quit();
                    xApp = null;
                }
            }
        }
示例#33
0
        //将listview中的数据导出到excel中
        private void DoExport(ListView listView, string strFileName)
        {
            int rowNum = listView.Items.Count;//行数
            int columnNum = listView.Items[0].SubItems.Count;//列数
            int rowIndex = 1;
            int columnIndex = 0;
            if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
            {
                return;
            }
            if (rowNum > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                object missing = System.Reflection.Missing.Value;//作为缺省值参数传给word或excel对象的某个函数
                Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];//取得sheet1
                Microsoft.Office.Interop.Excel.Range range = null;
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建excel对象,可能您的系统没有安装excel");
                    return;
                }
                xlApp.DefaultFilePath = "";
                xlApp.DisplayAlerts = true;
                //将ListView的列名导入Excel表第一行
                foreach (ColumnHeader dc in listView.Columns)
                {
                    columnIndex++;
                    xlSheet.Cells[rowIndex, columnIndex] = dc.Text;

                }
                //将ListView中的数据导入Excel中
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        //注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
                        xlSheet.Cells[rowIndex, columnIndex] = Convert.ToString(listView.Items[i].SubItems[j].Text) + "\t";
                        range = xlSheet.get_Range(xlSheet.Cells[rowIndex, columnIndex], xlSheet.Cells[rowIndex, columnIndex]);//表示获取要设置的单元格或单元格范围
                        range.Columns.AutoFit(); // 设置列宽为自动适应
                    }
                }
                xlSheet.SaveAs(strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                MessageBox.Show("日志导出成功");
                xlApp.Quit();
            }
        }
示例#34
0
        /// <summary>
        ///  OEM销售成绩表
        /// </summary>
        public static int ExportOEMSales(string templetFile, string outFile, DataTable dt, Hashtable ht)
        {
            object   missing = Missing.Value;
            DateTime beforeTime;
            DateTime afterTime;

            #region Excel文件初始化
            //只读属性的变更
            FileInfo fi = new FileInfo(templetFile);
            if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
            {
                fi.Attributes = FileAttributes.Normal;
            }
            //拷贝模版文件生成新文件
            if (File.Exists(templetFile))
            {
                try
                {
                    File.Copy(templetFile, outFile, true);
                }
                catch (Exception ex)
                {
                    Logger.Error("文件正在运行,重新生成文件失败。", ex);
                    return(CConstant.EXPORT_RUNNING);
                }
            }
            else
            {
                Logger.Error("模版文件不存在。", null);
                return(CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST);
            }

            beforeTime = DateTime.Now;

            //创建一个Application对象并使其可见
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = false;
            afterTime   = DateTime.Now;

            //打开模板文件,得到WorkBook对象
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(outFile, missing, missing, missing, missing, missing, missing,
                                                                                  missing, missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);

            //特定内容的替换
            foreach (DictionaryEntry de in ht)
            {
                try
                {
                    workSheet.Cells.Replace(de.Key, de.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
                catch
                {
                    continue;
                }
            }
            #endregion

            //数据
            int startRow = 8;
            int endRow   = dt.Rows.Count + startRow;

            int[] str = { 1, 2, 3, 5, 4, 6, 7, 10, 13, 14, 20 };

            for (int i = startRow; i < endRow; i++)
            {
                DataRow dr = dt.Rows[i - startRow];
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    workSheet.Cells[i, str[j]] = dr[j];
                }
            }

            //输出Excel文件并退出
            try
            {
                workBook.Save();
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook  = null;
                app       = null;

                GC.Collect();
            }
            catch (Exception ex)
            {
                try
                {
                    File.Delete(outFile);
                }
                catch (Exception e)
                {
                    Logger.Error("Excel文件保存,失败后复制文件的删除。", e);
                }
                Logger.Error("Excel文件保存失败。", null);
                return(CConstant.EXPORT_FAILURE);
            }
            finally
            {
                Process[] myProcesses;
                DateTime  startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID,暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }

            return(CConstant.EXPORT_SUCCESS);
        }
示例#35
0
        /// <summary>
        /// 创建一个空的Excel文件(2003版的Excel)
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string CreateBlankExcel(string path)
        {
            Microsoft.Office.Interop.Excel.Application excelApp;
            Microsoft.Office.Interop.Excel.Workbook excelDoc;

            excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            if (File.Exists(path))
            {
                File.Delete(path);
            }

            Object nothing = Missing.Value;

            excelDoc = excelApp.Workbooks.Add(nothing);

            Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;

            excelDoc.SaveAs(path, nothing, nothing, nothing, nothing, nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nothing, nothing, nothing, nothing, nothing);

            excelDoc.Close(nothing, nothing, nothing);

            excelApp.Quit();

            return path;
        }
        /// <summary>
        /// 把Microsoft.Office.Interop.Excel文件转换成PDF格式文件
        /// </summary>
        /// <param name="sourcePath">源文件路径</param>
        /// <param name="targetPath">目标文件路径</param>
        /// <returns>true=转换成功</returns>
        public static bool ExcelToPDF(string sourcePath, string targetPath)
        {
            targetPath = Regex.Replace(targetPath, " ", "", RegexOptions.IgnoreCase);//去除字符串中间的空格
            bool result = false;

            Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
            object missing = Type.Missing;

            Microsoft.Office.Interop.Excel.ApplicationClass application = null;
            Microsoft.Office.Interop.Excel.Workbook         workBook    = null;
            try
            {
                bool TimeOver             = false; //转换时间到了
                System.Timers.Timer timer = new System.Timers.Timer(30000);
                timer.AutoReset = false;           //只循环一次
                timer.Elapsed  += delegate(object sender, System.Timers.ElapsedEventArgs e)
                {
                    TimeOver = true;
                };
                timer.Start();
                Thread th = new Thread(new ThreadStart(delegate()
                {
                    try
                    {
                        application         = new Microsoft.Office.Interop.Excel.ApplicationClass();
                        application.Visible = false;
                        workBook            = application.Workbooks.Open(sourcePath);
                        workBook.Application.DisplayAlerts = false;
                        workBook.SaveAs();
                        workBook.ExportAsFixedFormat(targetType, targetPath);
                    }
                    catch
                    {
                    }
                }));
                th.IsBackground = true;//后台线程
                th.Start();
                //如果转换没有成功就一直循环
                while (th.ThreadState != System.Threading.ThreadState.Stopped)
                {
                    //如果一直没有转换成功,但是最大的转换时间到了也直接跳出While循环
                    if (TimeOver)
                    {
                        break;
                    }
                }
                if (th.ThreadState != System.Threading.ThreadState.Stopped)
                {
                    th.Abort();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                result = false;
            }
            finally
            {
                if (workBook != null)
                {
                    workBook.Close(true, missing, missing);
                    workBook = null;
                }
                if (application != null)
                {
                    application.Quit();
                    application = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            //判断文件是否转换成功
            if (File.Exists(targetPath))
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
示例#37
0
        public static Workbook Open(string excelFilename)
        {
            Workbook wb = new Workbook();

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(excelFilename,
                0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

            // Loop throught Worksheets
            Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = null;
            for(int w = 1; w <= excelWorkbook.Worksheets.Count; ++w)
            {
                excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[w];

                Worksheet ws = new Worksheet();
                ws.Name = excelWorksheet.Name;

                Microsoft.Office.Interop.Excel.Range usedRange = excelWorksheet.UsedRange;
                // convert the range to a System.Array
                System.Array array = (System.Array)usedRange.Cells.Value2;

                System.Diagnostics.Debug.WriteLine("Worksheet of name " + ws.Name);

                if (array != null)
                {
                    System.Diagnostics.Debug.WriteLine("Rows range from " + array.GetLowerBound(0) + " to " + array.GetUpperBound(0));
                    System.Diagnostics.Debug.WriteLine("Columns range from " + array.GetLowerBound(1) + " to " + array.GetUpperBound(1));

                    for (int i = array.GetLowerBound(0); i <= array.GetUpperBound(0); ++i)
                    {
                        for (int j = array.GetLowerBound(1); j <= array.GetUpperBound(1); ++j)
                        {
                            if (array.GetValue(i, j) == null)
                            {
                                //System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = null");
                            }
                            else
                            {
                                //System.Diagnostics.Debug.WriteLine("cell (" + i + "," + j + ") = " + array.GetValue(i, j).ToString());
                            }
                            // Note: Excel uses 1 based indices
                            if (array.GetValue(i, j) != null)
                            {
                                string sText = array.GetValue(i, j).ToString();
                                ws.SetText(i - 1, j - 1, sText);
                            }
                        }
                    }

                    wb.AddWorkSheet(ws);
                }
            }

            // Clean up
            excelWorkbook.Close(false, null, null);
            excelApp.Workbooks.Close();
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
            excelWorksheet = null;
            excelWorkbook = null;
            if (excelApp != null)
            {
                System.Diagnostics.Process[] pProcess;
                pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
                pProcess[0].Kill();
            }
            excelApp = null;
            GC.Collect();

            return wb;
        }
示例#38
0
        public bool DataSet2ExcelFile(DataSet ds, string OpenFileName, string SaveFileName)
        {
            string FilePath = SaveFileName;
            if (!Directory.Exists(Path.GetDirectoryName(FilePath)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(FilePath));
            }
            if (File.Exists(FilePath)) File.Delete(FilePath);

            //创建Application对象
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            xApp.Visible = false;
            //Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
            //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
            Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(OpenFileName,

            Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value
            , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
            //指定要操作的Sheet,两种方式:

            Microsoft.Office.Interop.Excel.Worksheet xSheetSample;
            List<int> savesheetid = new List<int>();
            foreach (DataTable dt in ds.Tables)
            {
                string tablename = dt.TableName;
                xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[tablename];

                //利用二维数组批量写入       ;
                string[,] ss = new string[dt.Rows.Count + 1, dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ss[0, i] = dt.Columns[i].ColumnName;
                }

                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    for (int k = 0; k < dt.Columns.Count; k++)
                    {
                        if (string.IsNullOrEmpty(dt.Rows[j][k].ToString()))
                        {
                            ss[j + 1, k] = "";
                        }
                        else
                        {
                            if (dt.Rows[j][k].ToString().Length > 3000)
                            {
                                ss[j + 1, k] = dt.Rows[j][k].ToString().Substring(0, 3000);
                            }
                            else
                            {
                                ss[j + 1, k] = dt.Rows[j][k].ToString();
                            }

                        }

                    }
                }

                Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xSheetSample.Cells[1, 1];
                range = range.get_Resize(dt.Rows.Count + 1, dt.Columns.Count);
                range.Value2 = ss;
                range.Value2 = range.Value2;
            }
            int sheetcount = xBook.Sheets.Count;
            xApp.DisplayAlerts = false;
            for (int i = sheetcount; i >= 1; i--)
            {
                if (!savesheetid.Contains(i))
                {
                    ((Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[i]).Delete();
                }
            }
            xApp.DisplayAlerts = false;

            //保存方式一:保存WorkBook
            xBook.SaveAs(FilePath,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);

            ////保存方式二:保存WorkSheet
            //xSheet.SaveAs(@"C:\CData2.xls",
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //保存方式三
            xBook.Save();
            xSheetSample = null;
            xBook = null;
            xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
            xApp = null;
            GC.Collect();
            KillProcess("EXCEL");
            System.Threading.Thread.Sleep(1000 * 3);
            return true;
        }
示例#39
0
        protected void m_exportButton_Click(object sender, EventArgs e)
        {
            //取得所有学生的数据
            string commandText = "select * from Stu";
            System.Data.DataTable dt = m_sqlHelper.Query(commandText);

            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            Microsoft.Office.Interop.Excel._Workbook xBk;
            Microsoft.Office.Interop.Excel._Worksheet xSt;
            xBk = excel.Workbooks.Add(true);
            xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;

            int i = 1;
            foreach (DataColumn col in dt.Columns)
            {
                xSt.Cells[1, i] = col.ColumnName.ToString();
                i++;
            }

            int dataRow = 2;
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    xSt.Cells[dataRow, k + 1] = dt.Rows[j][k].ToString();
                }
                dataRow++;
            }

            xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "Stu2014.xls");

            dt = null;
            xBk.Close(false, null, null);

            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            xBk = null;
            excel = null;
            xSt = null;
            GC.Collect();
            string path = Server.MapPath("Stu2014.xls");

            System.IO.FileInfo file = new System.IO.FileInfo(path);
            Response.Clear();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息,为"文件下载/另存为"对话框指定默认文件名
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
            // 添加头信息,指定文件大小,让浏览器能够显示下载进度
            Response.AddHeader("Content-Length", file.Length.ToString());

            // 指定返回的是一个不能被客户端读取的流,必须被下载
            Response.ContentType = "application/ms-excel";

            // 把文件流发送到客户端
            Response.WriteFile(file.FullName);
            // 停止页面的执行

            Response.End();
        }
示例#40
0
        public bool DataTable2ExcelFile(DataTable dt, string filename)
        {
            string FilePath = filename;
            if (!Directory.Exists(Path.GetDirectoryName(FilePath)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(FilePath));
            }
            if (File.Exists(FilePath)) File.Delete(FilePath);

            //创建Application对象
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

            xApp.Visible = false;
            //Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(@"C:\医药制造业\Sample.xls",
            //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
            Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value);

            Microsoft.Office.Interop.Excel.Worksheet xSheetSample = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];

            //利用二维数组批量写入       ;
            string[,] ss = new string[dt.Rows.Count + 1, dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ss[0, i] = dt.Columns[i].ColumnName;
            }

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    if (string.IsNullOrEmpty(dt.Rows[j][k].ToString()))
                    {
                        ss[j + 1, k] = "";
                    }
                    else
                    {
                        if (dt.Rows[j][k].ToString().Length > 800)
                        {
                            ss[j + 1, k] = "'" +dt.Rows[j][k].ToString().Substring(0, 800);
                        }
                        else
                        {
                            ss[j + 1, k] = "'" + dt.Rows[j][k].ToString();
                        }

                    }

                }
            }

            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xSheetSample.Cells[1, 1];
            range = range.get_Resize(dt.Rows.Count + 1, dt.Columns.Count);
            range.Value2 = ss;
            //range.Value2 = range.Value2;

            //保存方式一:保存WorkBook
            xBook.SaveAs(FilePath,
            Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);

            ////保存方式二:保存WorkSheet
            //xSheet.SaveAs(@"C:\CData2.xls",
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //保存方式三
            xBook.Save();
            xSheetSample = null;
            xBook = null;
            xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
            xApp = null;
            GC.Collect();
            //KillProcess("EXCEL");
            //System.Threading.Thread.Sleep(1000 * 3);
            return true;
        }
示例#41
0
        /// <summary>
        /// 数据库转为excel表格
        /// </summary>
        /// <param name="dataTable">数据库数据</param>
        /// <param name="SaveFile">导出的excel文件</param>
        public static void DataSetToExcel(MyDataBaseClass DataBase, string SaveFile)
        {
            Microsoft.Office.Interop.Excel.Application excel;

            Microsoft.Office.Interop.Excel._Workbook workBook;

            Microsoft.Office.Interop.Excel._Worksheet workSheet;

            object misValue = System.Reflection.Missing.Value;

            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            workBook = excel.Workbooks.Add(misValue);

            workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
            int rowIndex =1;
            int colIndex = 1;
            try
            {
                  //取得标题
            foreach (var col in DataBase.ColumnMember)
            {
                string value = col as string;
                excel.Cells[1, colIndex] = value;
                colIndex++;
            }
             int temp =  DataBase.RowCount;
            //取得表格中的数据
                 for (int i =1; i <= DataBase.RowCount; i++)
                 {
                     for (int j = 0; j < DataBase.ColumnCount; j++)
                     {
                     colIndex =i+1;
                     rowIndex = j+1 ;
                     int index = (i-1) * DataBase.ColumnCount + j;
                     string value = DataBase.RowMember[index] as string;
                     excel.Cells[colIndex, rowIndex] = value;
                     //设置表格内容居中对齐
                     ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[colIndex, rowIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    //设置表格自动适应大小
                     workSheet.Cells.Columns.AutoFit();
                     }
             }
             workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment =
                                 Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            }
            catch(Exception exp)
            {
                MessageBox.Show(exp.Message);
            }

            excel.Visible = false;
            workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,

                misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,

                misValue, misValue, misValue, misValue, misValue);

            workBook.Close(true, misValue, misValue);

            excel.Quit();

            PublicMethod.Kill(excel);//调用kill当前excel进程

            releaseObject(workSheet);

            releaseObject(workBook);

            releaseObject(excel);
        }
示例#42
0
 /// <summary>
 /// datagridview导出Excel,
 /// </summary>
 /// <param name="fname">保存的文件名</param>
 /// <param name="strCaption">标题行</param>
 /// <param name="myDGV">datagridview</param>
 /// <returns></returns>
 public static string ExportExcel(string fname, string strCaption, DataGridView myDGV)
 {
     string result = "";
     // 列索引,行索引,总列数,总行数
     int ColIndex = 0;
     int RowIndex = 0;
     int ColCount = myDGV.ColumnCount;
     int RowCount = myDGV.RowCount + 1;
     if (myDGV.RowCount == 0)
     {
         result = "没有数据可供导出";
     }
     // 创建Excel对象
     Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
     if (xlApp == null)
     {
         result = "创建Excel文件失败";
     }
     try
     {
         // 创建Excel工作薄
         Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
         Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
         // 设置标题
         Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同
         range.MergeCells = true;
         xlApp.ActiveCell.FormulaR1C1 = strCaption;
         xlApp.ActiveCell.Font.Size = 20;
         xlApp.ActiveCell.Font.Bold = true;
         xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
         // 创建缓存数据
         object[,] objData = new object[RowCount + 1, ColCount];
         //获取列标题
         foreach (DataGridViewColumn col in myDGV.Columns)
         {
             objData[RowIndex, ColIndex++] = col.HeaderText;
         }
         // 获取数据
         for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
         {
             for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
             {
                 if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string)
                     || myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";
                 {
                     objData[RowIndex, ColIndex] = "'" + myDGV[ColIndex, RowIndex - 1].Value;
                 }
                 else
                 {
                     objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value;
                 }
             }
             System.Windows.Forms.Application.DoEvents();
         }
         // 写入Excel
         range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]);
         range.Value2 = objData;
         //保存
         xlBook.Saved = true;
         xlBook.SaveCopyAs(fname);
         //返回值
         result = "成功导出,保存在c:\\";
     }
     catch (Exception)
     {
         result = "出现错误";
     }
     finally
     {
         xlApp.Quit();
         GC.Collect(); //强制回收
     }
     return result;
 }
示例#43
0
        ///////////////////////////////////////////////////////////////
        //メソッド名 : OutputExcel
        /// <summary>
        ///  指定GridviewのデータをExcelに出力する
        /// </summary>
        /// <param name="targetDataGridView">対象DataGridView</param>
        /// <param name="outputFilename"></param>
        /// <history>
        /// 日付    担当者   内容
        /// 2014/06/30 DatNT   新規作成
        /// </history>
        ///////////////////////////////////////////////////////////////
        private static void OutputExcel(DataGridView targetDataGridView,string outputFilename)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;

            try
            {
                // 保存確認ダイアログの表示
                SaveFileDialog dlg = new SaveFileDialog();

                dlg.FileName = outputFilename;
                dlg.Filter = "Excel files (*.xls)|*.xls";
                dlg.FilterIndex = 1;

                // OKボタンで終了した場合
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    object misValue = System.Reflection.Missing.Value;

                    xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    xlWorkBook = xlApp.Workbooks.Add(misValue);
                    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    int i = 0;
                    int j = 0;

                    for (i = 0; i <= targetDataGridView.Rows.Count - 1; i++)
                    {
                        for (j = 0; j <= targetDataGridView.Columns.Count - 1; j++)
                        {
                            if (targetDataGridView.Columns[j].Visible)
                            {
                                DataGridViewCell cell = targetDataGridView[j, i];

                                if (i == 0)
                                {
                                    DataGridViewHeaderCell h = targetDataGridView.Columns[j].HeaderCell;
                                    xlWorkSheet.Cells[i + 1, j + 1] = h.Value;
                                }

                                // Code columns format
                                if (targetDataGridView.Columns[j].Name.Length > 5
                                    && targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "CdCol")
                                {
                                    xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value;
                                    Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, j + 1];
                                    rng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                                    continue;
                                }

                                if (targetDataGridView.Columns[j].Name == "NameKbnCol")
                                {
                                    xlWorkSheet.Cells[i + 2, j + 1] = cell.FormattedValue.ToString();

                                    continue;
                                }

                                if (targetDataGridView.Columns[j].Name == "NameCol")
                                {
                                    xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value;

                                    continue;
                                }

                                xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
                            }
                        }
                    }

                    xlWorkBook.SaveAs(dlg.FileName,
                                        Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                                        misValue,
                                        misValue,
                                        misValue,
                                        misValue,
                                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                                        misValue,
                                        misValue,
                                        misValue,
                                        misValue,
                                        misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();

                }
            }
            catch
            {
                throw;
            }
            finally
            {
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
        }
示例#44
0
        private void ListToko()
        {
            DataTable da = dtToko.DefaultView.ToTable().Copy();

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Application.Workbooks.Add(Type.Missing);

            progressBar1.Visible = true;
            progressBar1.Maximum = dataGridToko.Rows.Count;
            progressBar1.Value   = 0;
            //for (int i = 1; i < dataGridToko.Columns.Count + 1; i++)
            //{
            //    //ExcelApp.Cells[1, i] = dataGridToko.Columns[i - 1].HeaderText;
            //    ExcelApp.Cells[1, 1] = dataGridToko.Columns[0].HeaderText;
            //    ExcelApp.Cells[1, 2] = dataGridToko.Columns[1].HeaderText;
            //}

            ExcelApp.Cells[1, 1]  = "Nama Toko";
            ExcelApp.Cells[1, 2]  = "Id.Wil";
            ExcelApp.Cells[1, 3]  = "Id.Toko";
            ExcelApp.Cells[1, 4]  = "K";
            ExcelApp.Cells[1, 5]  = "Alamat";
            ExcelApp.Cells[1, 6]  = "Kota";
            ExcelApp.Cells[1, 7]  = "Daerah";
            ExcelApp.Cells[1, 8]  = "Propinsi";
            ExcelApp.Cells[1, 9]  = "No.Telp";
            ExcelApp.Cells[1, 10] = "Penanggung Jawab";
            ExcelApp.Cells[1, 11] = "Hari Sales";
            ExcelApp.Cells[1, 12] = "Hari Kirim";
            ExcelApp.Cells[1, 13] = "Status";

            int x = 0;
            int i = 0;

            foreach (DataRow dr in da.Rows)
            {
                Application.DoEvents();
                this.Invalidate();

                progressBar1.Value = x;
                x++;
                ExcelApp.Cells[i + 2, 1]  = Tools.isNull(dr["NamaToko"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 2]  = Tools.isNull(dr["WilID"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 3]  = Tools.isNull("'" + dr["TokoID"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 4]  = Tools.isNull(dr["Cabang2"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 5]  = Tools.isNull(dr["Alamat"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 6]  = Tools.isNull(dr["Kota"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 7]  = Tools.isNull(dr["Daerah"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 8]  = Tools.isNull(dr["Propinsi"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 9]  = Tools.isNull("'" + dr["Telp"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 10] = Tools.isNull(dr["PenanggungJawab"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 11] = Tools.isNull(dr["HariSales"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 12] = Tools.isNull(dr["JangkaWaktuKredit"], "").ToString().Trim();
                ExcelApp.Cells[i + 2, 13] = Tools.isNull(dr["StatusAktif"], "").ToString().Trim();
                i++;
            }


            //for (int i = 0; i < dataGridToko.Rows.Count; i++)
            //{
            //    Application.DoEvents();
            //    this.Invalidate();

            //    progressBar1.Value = x;
            //    x++;
            //    for (int j = 0; j < dataGridToko.Columns.Count; j++)
            //    {
            //        //ExcelApp.Cells[i + 2, j + 1] = dataGridToko.Rows[i].Cells[j].Value.ToString();
            //        //ExcelApp.Cells[i + 2, j + 1] = Tools.isNull(dataGridToko.Rows[i].Cells[j].Value,"").ToString();
            //          ExcelApp.Cells[i + 2, 1] = Tools.isNull(dataGridToko.Rows[i].Cells[0].Value, "").ToString();
            //          ExcelApp.Cells[i + 2, 2] = Tools.isNull(dataGridToko.Rows[i].Cells[1].Value, "").ToString();

            //    }
            //}



            ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Temp\\Toko.xls");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
            progressBar1.Visible = false;
        }
示例#45
0
        public static string ExcelMultiSheet(DataSet ds)
        {
            string filepath;

            Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            excelApp.Application.Workbooks.Add();

            DataTable table100      = ds.Tables[0];
            DataTable table50first  = ds.Tables[1];
            DataTable table50second = ds.Tables[2];
            DataTable table50full   = ds.Tables[3];

            Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[1];
            sheet1.Name = "100% MPA";

            for (int i = 0; i < table100.Columns.Count; i++)
            {
                sheet1.Cells[1, i + 1] = table100.Columns[i].ColumnName;
            }

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

            Microsoft.Office.Interop.Excel.Worksheet sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[2];
            sheet2.Name = "50% MPA 1st 50%";

            for (int i = 0; i < table50first.Columns.Count; i++)
            {
                sheet2.Cells[1, i + 1] = table50first.Columns[i].ColumnName;
            }

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

            Microsoft.Office.Interop.Excel.Worksheet sheet3 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[3];
            sheet3.Name = "50% MPA 2nd 50%";

            for (int i = 0; i < table50second.Columns.Count; i++)
            {
                sheet3.Cells[1, i + 1] = table50second.Columns[i].ColumnName;
            }

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

            excelApp.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);

            Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[4];
            //Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets.Add(After: excelApp.Sheets[excelApp.Sheets.Count]);
            sheet4.Name = "50% MPA 100% 2pcs & 1+1 pcs";

            for (int i = 0; i < table50full.Columns.Count; i++)
            {
                sheet4.Cells[1, i + 1] = table50full.Columns[i].ColumnName;
            }

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

            filepath = @"C:\ReceivedPO\" + DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO.xls";

            string filename = "";

            SaveFileDialog sfd = new SaveFileDialog()
            {
                DefaultExt  = "xls",
                Filter      = "EXCEL Files (*.xls)|*.xls",
                FilterIndex = 1,
                FileName    = DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO"// + year + month + day,
            };

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    filename = sfd.FileName;

                    excelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

                    //excelApp.ActiveWorkbook.Saved = true;
                    //excelApp.AlertBeforeOverwriting = false;
                    excelApp.Quit();

                    MessageBox.Show("Record saved to " + filename);
                }
                catch (COMException ex)
                {
                    MessageBox.Show(ex.Message + ex.StackTrace);

                    Debug.WriteLine(ex.Message + ex.StackTrace);
                }
            }
            return(filename);
        }
        private void CsvExportButton_Click(object sender, RoutedEventArgs e)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                //Обрати таблицю з ComboBox
                ComboBoxItem comboItem = (ComboBoxItem)ComboBox.SelectedItem;
                string       name      = comboItem.Name;

                //запит для виводу всіх даних з таблиці
                string     cmdText = String.Format("SELECT * FROM {0}", name);
                SqlCommand cmd     = new SqlCommand(cmdText, con);

                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);

                        DataColumnCollection dcCollection = dt.Columns;

                        //ЕКспорт даних в лист EXCEL

                        Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

                        excelApp.Application.Workbooks.Add(Type.Missing);

                        // ExcelApp.Cells.CopyFromRecordset(objRS);

                        for (int i = 1; i < dt.Rows.Count + 1; i++)
                        {
                            for (int j = 1; j < dt.Columns.Count + 1; j++)
                            {
                                if (i == 1)
                                {
                                    excelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                                }
                                else
                                {
                                    excelApp.Cells[i, j] = dt.Rows[i - 1][j - 1].ToString();
                                }
                            }
                        }
                        //Діалог збереження файлу
                        Microsoft.Win32.SaveFileDialog dialog = new Microsoft.Win32.SaveFileDialog();
                        //Ім'я файлу за замовчуванням
                        dialog.Title    = "Збереження";
                        dialog.FileName = comboItem.Name + " " + DateTime.Now.ToString("d-M-yyyy");
                        dialog.Filter   = "Таблиця Excel|*.xlsx";

                        if (dialog.ShowDialog() == true)
                        {
                            //збереження xlsx файлу
                            excelApp.ActiveWorkbook.SaveCopyAs(dialog.FileName);
                            excelApp.ActiveWorkbook.Saved = true;
                            excelApp.Quit();
                            //Повідомлення про успішне експортування
                            MessageBox.Show("Експорт виконано успішно!", "Повідомлення",
                                            MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                    }
                }
            }
        }
示例#47
0
        /// <summary>
        /// 采购注文书国外
        /// </summary>
        public static int ExportPurchaseOverseas(string templetFile, string outFile, DataTable dt, Hashtable ht)
        {
            object   missing = Missing.Value;
            DateTime beforeTime;
            DateTime afterTime;

            #region Excel文件初始化
            //只读属性的变更
            FileInfo fi = new FileInfo(templetFile);
            if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
            {
                fi.Attributes = FileAttributes.Normal;
            }
            //拷贝模版文件生成新文件
            if (File.Exists(templetFile))
            {
                try
                {
                    File.Copy(templetFile, outFile, true);
                }
                catch (Exception ex)
                {
                    Logger.Error("文件正在运行,重新生成文件失败。", ex);
                    return(CConstant.EXPORT_RUNNING);
                }
            }
            else
            {
                Logger.Error("模版文件不存在。", null);
                return(CConstant.EXPORT_TEMPLETE_FILE_NOT_EXIST);
            }

            beforeTime = DateTime.Now;

            //创建一个Application对象并使其可见
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = false;
            afterTime   = DateTime.Now;

            //打开模板文件,得到WorkBook对象
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(outFile, missing, missing, missing, missing, missing, missing,
                                                                                  missing, missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);

            //特定内容的替换
            foreach (DictionaryEntry de in ht)
            {
                try
                {
                    workSheet.Cells.Replace(de.Key, de.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
                catch
                {
                    continue;
                }
            }
            #endregion

            //数据
            int startRow = 19;
            int endRow   = dt.Rows.Count + startRow;
            int addLine  = 0;

            for (int i = startRow; i < endRow; i++)
            {
                if (i >= 36 && i < endRow - 1)
                {
                    Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
                    range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
                                           Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                    addLine++;
                }
                DataRow dr = dt.Rows[i - startRow];

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Columns[j].ColumnName.Contains("X_"))
                    {
                        continue;
                    }
                    if (dr[j].GetType() == typeof(string))
                    {
                        workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
                    }
                    else
                    {
                        workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
                    }
                }
            }

            startRow = 78 + addLine;
            endRow   = dt.Rows.Count + startRow;
            for (int i = startRow; i < endRow; i++)
            {
                if (i >= (95 + addLine) && i < endRow - 1)
                {
                    Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[i, Type.Missing];
                    range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
                                           Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                }
                DataRow dr = dt.Rows[i - startRow];

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Columns[j].ColumnName.Contains("X_"))
                    {
                        continue;
                    }
                    if (dr[j].GetType() == typeof(string))
                    {
                        workSheet.Cells[i, j + 1] = "'" + Convert.ToString(dr[j]);
                    }
                    else
                    {
                        workSheet.Cells[i, j + 1] = Convert.ToString(dr[j]);
                    }
                }
            }

            #region  输出Excel文件并退出
            //输出Excel文件并退出
            try
            {
                workBook.Save();
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook  = null;
                app       = null;

                GC.Collect();
            }
            catch (Exception ex)
            {
                try
                {
                    File.Delete(outFile);
                }
                catch (Exception e)
                {
                    Logger.Error("Excel文件保存,失败后复制文件的删除。", e);
                }
                Logger.Error("Excel文件保存失败。", null);
                return(CConstant.EXPORT_FAILURE);
            }
            finally
            {
                Process[] myProcesses;
                DateTime  startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID,暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }
            #endregion

            return(CConstant.EXPORT_SUCCESS);
        }
示例#48
0
        public static void SaveAsExcel(string name, DataTable dt)
        {
            OleDbConnection cnnxls = new OleDbConnection(string.Format(excelstring, name));
            string insertsql = "";
            string insertcolumnstring = "";
            string insertvaluestring = "";
            string fileName = name + ".xls";
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            excel.Application.Workbooks.Add(true);
            int colIndex = 0;
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
                insertcolumnstring += string.Format("{0},", col.ColumnName);
            }
            excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //������¼
            //conn.execute(sql);

            insertcolumnstring = insertcolumnstring.Trim(',');
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    row[dc].ToString();
                    insertvaluestring += string.Format("'{0}',", row[dc].ToString().Replace("'", "''"));
                }
                string sql = string.Format("insert into [Sheet1$]({0}) values({1})", insertcolumnstring, insertvaluestring);
                OleDbDataAdapter myDa = new OleDbDataAdapter(sql, cnnxls);
                myDa.InsertCommand.ExecuteNonQuery();
            }
            excel.Visible = false;
            //excel.ActiveWorkbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();//��������
        }
示例#49
0
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        public static int DataTableToExcel(DataTable dt, string title, string columns, string sheetName, string fileName)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter      = "导出Excel (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.Title       = "导出文件保存路径";
            saveFileDialog.FileName    = "LZ_" + fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excel.Application.Workbooks.Add(true);;
                    excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。
                    if (excel == null)
                    {
                        Logger.Error("Excel文件保存失败。", null);
                        return(CConstant.EXPORT_FAILURE);
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook  book  = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = sheetName;
                    int m = 0, n = 0;
                    //生成列名称 这里i是从1开始的 因为我第0列是个隐藏列ID 没必要写进去
                    string[] strHeader = title.Split(',');
                    string[] strColumns = columns.Split(',');
                    for (int i = 0; i < strHeader.Length; i++)
                    {
                        excel.Cells[1, i + 1] = strHeader[i].ToString();
                    }
                    //填充数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        //j也是从1开始 原因如上 每个人需求不一样
                        int j = 1;
                        foreach (string str in strColumns)
                        {
                            try
                            {
                                if (dr[str.Trim()].GetType() == typeof(string))
                                {
                                    excel.Cells[i + 2, j] = "'" + dr[str.Trim()].ToString();
                                    j++;
                                }
                                else
                                {
                                    excel.Cells[i + 2, j] = dr[str.Trim()].ToString();
                                    j++;
                                }
                            }
                            catch
                            {
                                j++;
                                continue;
                            }
                        }
                    }
                    try
                    {
                        sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                        book.Close(false, miss, miss);
                        books.Close();
                        excel.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                        GC.Collect();
                        //MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //System.Diagnostics.Process.Start(strName);
                    }
                    catch (Exception ex)
                    {
                        Logger.Error("Excel文件保存失败。", ex);
                        return(CConstant.EXPORT_FAILURE);
                    }
                }
                return(CConstant.EXPORT_SUCCESS);
            }
            else
            {
                return(CConstant.EXPORT_CANCEL);
            }
            return(CConstant.EXPORT_SUCCESS);
        }