Пример #1
0
 /// <summary>
 /// Excel文件打印预览
 /// </summary>
 /// <param name="ExcelFile">文件路径包含文件名称</param>
 public static void ExcelPreview(string ExcelFile)
 {
     try
     {
         Excel.Application xlsApp = new Excel.Application();
         if (xlsApp == null)
         {
             throw new Exception("无法创建Excel对象,可能您的计算机未安装Excel");
         }
         Excel.Workbooks xlsWbs = xlsApp.Workbooks;
         Excel.Workbook  xlsWb  = xlsWbs.Open(
             ExcelFile, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value, Missing.Value, Missing.Value);
         xlsApp.Visible = true;
         xlsWb.PrintPreview(false);
         xlsWb = null;
         xlsApp.Quit();
         xlsApp = null;
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message + "Excel文件操作失败");
     }
 }
Пример #2
0
        /// <summary>
        /// 将datatable的数据导出到excel
        /// </summary>
        /// <param name="mdt"></param>
        private void DataToExcel(DataTable mdt)
        {
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            Excel.Workbooks        oBooks = oExcel.Workbooks;

            Excel._Workbook oBook = null;

            oBook = (Excel._Workbook)(oExcel.Workbooks.Add(true));// 引用excel工作薄

            for (int i = 0; i < mdt.Columns.Count; i++)
            {
                oExcel.Cells[2, i + 1] = mdt.Columns[i].ColumnName.ToString();// m_DataView.Columns[i].HeaderText.ToString();
            }

            for (int i = 0; i < mdt.Rows.Count; i++)
            {
                for (int j = 0; j < mdt.Columns.Count; j++)
                {
                    oExcel.Cells[i + 3, j + 1] = mdt.Rows[i][j].ToString();
                }
            }

            oExcel.Visible = true;
            object Missing = System.Reflection.Missing.Value;

            //  oExcel.Run("Sheet1.printdoc", Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);

            oBook.Application.DisplayAlerts = false;
        }
Пример #3
0
 /// <summary>
 /// 功能:实现Excel应用程序的打开
 /// </summary>
 /// <param name="TemplateFilePath">模板文件物理路径</param>
 public void Open(string TemplateFilePath)
 {
     //打开对象
     m_objExcel               = new Microsoft.Office.Interop.Excel.Application();
     m_objExcel.Visible       = false;
     m_objExcel.DisplayAlerts = false;
     if (m_objExcel.Version != "11.0")
     {
         MessageBox.Show("您的Excel 版本不是11.0 (Office 2003),操作可能会出现问题。");
         m_objExcel.Quit();
         return;
     }
     m_objBooks = (Workbooks)m_objExcel.Workbooks;
     if (TemplateFilePath.Equals(String.Empty))
     {
         m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
     }
     else
     {
         m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
     }
     m_objSheets = (Sheets)m_objBook.Worksheets;
     m_objSheet  = (_Worksheet)(m_objSheets.get_Item(1));
     m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
 }
Пример #4
0
        /// <summary>
        /// 以excelOpenFileName为模板新建Excel文件
        /// </summary>
        public bool OpenExcelFile()
        {
            if (xlsApp != null)
            {
                //检查文件是否存在
                if (xlsOpenFileName == "")
                {
                    throw new Exception("请选择文件!");
                }
            }
            if (!File.Exists(xlsOpenFileName))
            {
                throw new Exception(xlsOpenFileName + "该文件不存在!");
            }
            try
            {
                xlsApp = new Excel.ApplicationClass();
                xlsWbs = xlsApp.Workbooks;
                xlsWb  = ((Excel.Workbook)xlsWbs.Open(xlsOpenFileName, Missing.Value, Missing.Value,
                                                      Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                      Missing.Value, Missing.Value, Missing.Value, Missing.Value));
                xlsWs = (Excel.Worksheet)xlsWb.Worksheets[excelActiveWorkSheetIndex];

                xlsApp.Visible = false;
                return(true);
            }
            catch (Exception e)
            {
                CloseExcelApplication();
                throw new Exception("(1)没有安装Excel 2003;\n(2)或没有安装Excel 2003 .NET 可编程性支持;\n详细信息:\n" + e.Message);
            }
        }
Пример #5
0
 //--------------------------------------------------------------------------------------------------------
 /// <summary>
 /// 关闭Excel文件,释放对象;最后一定要调用此函数,否则会引起异常
 /// </summary>
 /// <param></param>
 public void CloseExcelApplication()
 {
     try
     {
         xlsWbs = null;
         xlsWb  = null;
         xlsWs  = null;
         xlsRg  = null;
         if (xlsApp != null)
         {
             xlsApp.ActiveWorkbook.Close(false, null, null);
             xlsApp.Workbooks.Close();
             //Object missing = Type.Missing;
             xlsApp.Quit();
             xlsApp = null;
             //ReleaseAllRef(excelApplication);//Error
         }
     }
     finally
     {
         GC.Collect();
         GC.WaitForPendingFinalizers();
         GC.Collect();
         GC.WaitForPendingFinalizers();
     }
 }
Пример #6
0
        // 运行excel的vba宏
        public object RunExcelMacro(string strMacroName, object args)
        {
            LuaTable ltArgs = args as LuaTable;

            if (ltArgs == null)
            {
                MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空!");
                return(null);
            }

            object[] oArgArr = new object[ltArgs.Values.Count];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                oArgArr[i] = ltArgs[i];
            }

            object[] ArgArr30 = new object[30];
            for (int i = 0; i < oArgArr.Length; i++)
            {
                ArgArr30[i] = oArgArr[i];
            }
            for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
            {
                ArgArr30[i] = Missing.Value;
            }

            if (excelApp == null) // 未初始化成功
            {
                MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
                return(null); // 脚本已经对null值的处理
            }

            object result_macro = null;

            try
            {
                result_macro = excelApp.Run(strMacroName,
                                            ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
                                            ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
                                            ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
                                            ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
                                            ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
            }
            catch (Exception ex)
            {
                MessageBox.Show("宏运行错误,请不要保存所作修改!");
            }

            return(result_macro as string[]);

#if false
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook  = null;
            string          strXL  = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
            oBook = oBooks.Open(strXL, oMissing, oMissing,
                                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            RunMacro(oExcel, new Object[] { "矩形1_Click" });
Пример #7
0
 public ClsAllExcel()
 {
     xlsApp           = null;
     xlsWbs           = null;
     xlsWb            = null;
     xlsWs            = null;
     ActiveSheetIndex = 1;
 }
Пример #8
0
 public void Open(string FileName)//打开一个Excel文件
 {
     app = new Excel.Application();
     wbs = app.Workbooks;
     wb  = wbs.Add(FileName);
     //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
     //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
     mFilename = FileName;
 }
Пример #9
0
 public Base()
 {
     App               = new Application();
     App.Visible       = true;
     App.DisplayAlerts = false;
     Books             = App.Workbooks;
     Book              = Books.Add(Missing.Value);
     Sheet             = Book.ActiveSheet;
 }
Пример #10
0
 //关闭一个Excel对象,销毁对象
 public void Close()
 {
     //wb.Save();
     wb.Close(Type.Missing, Type.Missing, Type.Missing);
     wbs.Close();
     app.Quit();
     wb  = null;
     wbs = null;
     app = null;
     GC.Collect();
 }
Пример #11
0
 public void Quit()
 {
     Book.Close();
     App.Quit();
     Marshal.ReleaseComObject(Book);
     Marshal.ReleaseComObject(Books);
     Book  = null;
     Books = null;
     rng   = null;
     App   = null;
     Sheet = null;
     GC.Collect();
 }
Пример #12
0
 private void Dispose(bool disposing)
 {
     if (disposing)
     {
         // Dispose managed resources.
         Marshal.FinalReleaseComObject(m_objExcel);
         m_objRange  = null;
         m_objSheet  = null;
         m_objSheets = null;
         m_objBooks  = null;
         m_objBook   = null;
         m_objExcel  = null;
     }
 }
Пример #13
0
    private void ExportExcel(DataTable dt)
    {
        if (dt == null)
        {
            return;
        }
        Excel.Application xlApp = new Excel.Application();

        if (xlApp == null)
        {
            return;
        }
        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        Excel.Workbooks workbooks = xlApp.Workbooks;
        Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
        Excel.Range     range;
        long            totalCount = dt.Rows.Count;
        long            rowRead    = 0;
        float           percent    = 0;

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            worksheet.Cells[1, i + 1] = transColumnName(dt.Columns[i].ColumnName);
            range = (Excel.Range)worksheet.Cells[1, i + 1];
            range.Interior.ColorIndex = 15;
            range.Font.Bold           = true;
        }

        for (int r = 0; r < dt.Rows.Count; r++)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
            }
            rowRead++;
            percent = ((float)(100 * rowRead)) / totalCount;
        }
        xlApp.Visible = true;
    }
Пример #14
0
 /// <summary>
 /// 新建一个Excel实例
 /// </summary>
 /// <param name="strTitle">Excel表头上的文字</param>
 public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
 {
     m_objExcel         = new Excel.ApplicationClass();
     m_objExcel.Visible = true;
     m_objBooks         = (Excel.Workbooks)m_objExcel.Workbooks;
     m_objBook          = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
     m_objSheets        = (Excel.Sheets)m_objBook.Worksheets;
     if (intSheetTotalSize <= 3)
     {
         if (this.dbTotalSize <= this.dbSheetSize)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else if (this.dbTotalSize <= this.dbSheetSize * 2)
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
         else
         {
             this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
             this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
             return;
         }
     }
     for (int i = 3; i < intSheetTotalSize; i++)
     {
         m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
     }
     ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
     for (int i = 2; i <= m_objSheets.Count; i++)
     {
         ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
     }
 }
Пример #15
0
        static void Main(string[] args)
        {
            Console.WriteLine("Interop Performance Test - 10.000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;
            //excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);

            /*
             *  create new empty worksheet
             */
            Excel.Workbooks books  = excelApplication.Workbooks;
            Excel.Workbook  book   = books.Add(Missing.Value);
            Excel.Sheets    sheets = book.Worksheets;
            Excel.Worksheet sheet  = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            /*
             *  do the test
             *  we collect all references and release after time recording
             *  the 2 latebind libs release the references at end automaticly and we want a fair test
             */
            List <object> comReferenesList = new List <object>();
            DateTime      timeStart        = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                string rangeAdress = "$A" + i.ToString();
                Range  cellRange   = (Range)sheet.Range[rangeAdress];
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";
                cellRange.WrapText     = false;
                Comment sampleComment = cellRange.AddComment("Sample Comment");
                comReferenesList.Add(cellRange);
                comReferenesList.Add(sampleComment);
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            foreach (var item in comReferenesList)
            {
                Marshal.ReleaseComObject(item);
            }

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "Interop.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            /*
             * release & quit
             */
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            excelApplication.Quit();
            Marshal.ReleaseComObject(excelApplication);
        }
Пример #16
0
        /// <summary>
        /// 将datatable导出到excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="saveasfilepath"></param>
        /// <param name="WorkbookName"></param>
        public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName)
        {
            if (dt == null)
            {
                return;
            }
            string saveFileName = saveasfilepath;  // "d:\\333.xlsx";
            bool   fileSaved    = false;

            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                throw new Exception("无法创建Excel对象,可能您的机子未安装Excel");
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            if (!string.IsNullOrEmpty(WorkbookName))
            {
                Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
                ws.Name = WorkbookName;
            }
            Excel.Range range;
            //string oldCaption = DateTime.Today.ToString("yy-MM-dd");
            long  totalCount = dt.Rows.Count;
            long  rowRead    = 0;
            float percent    = 0;

            // worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd");
            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold           = true;
            }
            //写入数值

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                //this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.
                System.Windows.Forms.Application.DoEvents();
            }
            //this.lbl_process.Visible = false;

            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle  = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight     = Excel.XlBorderWeight.xlThin;

            if (dt.Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle  = Excel.XlLineStyle.xlContinuous;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight     = Excel.XlBorderWeight.xlThin;
            }

            if (saveFileName != "")
            {
                try
                {
                    //xlApp.Visible = true;
                    //workbook.PrintPreview(false);
                    workbook.Close(true, saveFileName, Type.Missing);
                    workbook = null;
                    //SaveAs方法会截断超过255个字符的字符串
                    //workbook.Saved = true;
                    //workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing,
                    //    Type.Missing, Type.Missing, Type.Missing,
                    //    Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                    //    Type.Missing, Type.Missing, Type.Missing);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    xlApp.Quit();
                    GC.Collect();
                    fileSaved = false;
                    throw new Exception("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            xlApp = null;
            GC.Collect();//强行销毁
            if (fileSaved && File.Exists(saveFileName))
            {
                //System.Diagnostics.Process.Start(saveFileName);
            }
        }
Пример #17
0
        /// <summary>
        /// 把DataTable中的数据导出到Excel
        /// </summary>
        /// <param name="dt"></param>
        public static void DataTableToExcel(System.Data.DataTable srcDt, string savename)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = srcDt;

            if (dt == null)
            {
                return;
            }

            string saveFileName = "";
            bool   fileSaved    = false;

            /*SaveFileDialog saveDialog = new SaveFileDialog();
             * saveDialog.DefaultExt = "xlsx";
             * saveDialog.Filter = "Excel文件|*.xlsx";
             * saveDialog.FileName = "导出文件";
             * saveDialog.ShowDialog();
             * saveFileName = saveDialog.FileName;
             * if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }
            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);


            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth     = 40;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight      = 50;

            //worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText          = true;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            for (int i = 2; i < 12; i++)
            {
                if (dt.Rows[i - 2][3].ToString() == "E")
                {
                    worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
                }
                if (dt.Rows[i - 2][3].ToString() == "W")
                {
                    worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
                }
            }
            //写入字段

            worksheet.Cells[1, 1] = "参数名称";
            worksheet.Cells[1, 2] = "参数值";
            worksheet.Cells[1, 3] = "推荐值";
            worksheet.Cells[1, 4] = "报警值";
            worksheet.Cells[1, 5] = "详情";
            worksheet.Cells[1, 6] = "参数类型";
            worksheet.Cells[1, 7] = "重要级别";
            worksheet.Cells[1, 8] = "历史变化";


            //写入数值

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                }
                System.Windows.Forms.Application.DoEvents();
            }
            //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

            /*string md5 = "";
             * for (int r = 0; r < 5; r++)
             * {
             *  for (int i = 0; i < 8; i++)
             *  {
             *      md5 += dt.Rows[r][i].ToString();
             *      worksheet.Cells[2, 9] = md5;
             *  }
             *
             * }
             */

            //worksheet.Cells.Width = 39;
            //if (saveFileName != "")
            //{
            try
            {
                workbook.Saved = true;
                saveFileName   = savename;
                workbook.SaveCopyAs(saveFileName);
                fileSaved = true;
                MessageBox.Show("导出完成!");
            }
            catch (Exception ex)
            {
                fileSaved = false;
                MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
            }
            //}
            //else
            //{
            // fileSaved = false;
            //}

            xlApp.Quit();
            GC.Collect();//强行销毁
            if (fileSaved && System.IO.File.Exists(saveFileName))
            {
                System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
            }
        }
Пример #18
0
    public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
    {
        //文件存在时先删除文件后再进行下一步操作
        FileInfo file = new FileInfo(strFullFilePath);

        if (file.Exists)
        {
            file.Delete();
        }
        int rowIndex = 3; //开始写入数据的单元格行
        int colIndex = 0; //开始写入数据的单元格列

        System.Reflection.Missing miss   = System.Reflection.Missing.Value;
        Excel.ApplicationClass    mExcel = new Excel.ApplicationClass();
        mExcel.Visible = false;
        Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
        Excel.Workbook  mBook  = (Excel.Workbook)(mBooks.Add(miss));
        Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
        Excel.Range     er     = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
        er.Value2 = title;
        try
        {
            foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
            {
                colIndex++;
                mSheet.Cells[3, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows) //同样方法处理数据
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    if (colIndex == 2)
                    {
                        mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
                    }
                    else
                    {
                        mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
            }
            //保存工作已写入数据的工作表
            mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            // return true;
        }
        catch (Exception ee)
        {
            throw new Exception(ee.Message);
        }
        finally //finally中的代码主要用来释放内存和中止进程()
        {
            mBook.Close(false, miss, miss);
            mBooks.Close();
            mExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
            GC.Collect();
        }
        //return false;
    }
Пример #19
0
        public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(filePath));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
            _excelSheet.Name = sheetName;
        }
Пример #20
0
        public void OpenExcelApp(bool visible)
        {
            _excelApplication = new Excel.Application();
            _excelApplication.Visible = visible;

            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            _workBook = (Excel._Workbook)(_workBooks.Add(_value));

            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
        }
Пример #21
0
    public static string ProcesarArchivo(string pathArchivo)
    {
        string            msgResul     = "Migración realizada correctamente.";
        ImportadorExcelBE infoImportar = new ImportadorExcelBE();

        UIUtility.COMObjectAplication ObjCom = null;
        Excel.Application             xlApp  = null;

        try
        {
            ObjCom = new UIUtility.COMObjectAplication("Excel.Application", "EXCEL");
            xlApp  = (Excel.Application)ObjCom.ObjetoAplication;

            Excel.Workbooks oBooks = xlApp.Workbooks;
            oBooks.Open(pathArchivo, ReadOnly: true, IgnoreReadOnlyRecommended: true);

            Excel.Workbook xlLibro = oBooks.Item[1];
            Excel.Sheets   oSheets = xlLibro.Worksheets;

            Excel.Worksheet hojaConfig = (Excel.Worksheet)oSheets.Item[1];

            DataTable dtConfig = ObtenerConfigPrincipal(infoImportar, hojaConfig);

            int             indiceHojaParaMigrar;
            Excel.Worksheet hojaParaMigrar;

            foreach (DataRow infoTabla in dtConfig.Rows)
            {
                indiceHojaParaMigrar = ObtenerIndiceHojaExcel(oSheets, infoTabla["Pestania"].ToString());
                if (indiceHojaParaMigrar > 0)
                {
                    hojaParaMigrar = (Excel.Worksheet)oSheets.Item[indiceHojaParaMigrar];

                    DataTable dtDefTablas = null;
                    dtDefTablas = ObtenerConfigColumnasSegunTabla(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());

                    DataTable dtDatos = null;
                    dtDatos = ObtenerRegistrosTablaSegunPestania(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
                }
                else
                {
                    throw new ImportadorExcelExceptionBE("No se ha encontrado la Hoja Excel: " + infoTabla["Pestania"].ToString());
                }
            }

            ImportadorExcelBM objImportadorBM = new ImportadorExcelBM();
            objImportadorBM.GuardarImportacion(infoImportar);
        }
        catch (Exception ex)
        {
            if (xlApp != null)
            {
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();

            msgResul = ex.Message;
        }
        finally
        {
            if (ObjCom != null)
            {
                ObjCom.terminarProceso();
            }
        }

        return(msgResul); // Si es "" (VACIO) entonces es PROCESO OK
    }
Пример #22
0
 public void Create()//创建一个Excel对象
 {
     app = new Excel.Application();
     wbs = app.Workbooks;
     wb  = wbs.Add(true);
 }
Пример #23
0
        private void ExportUpEmployee()
        {
            string       strSql = @"select  GetOrgName(GetStationOrgID(b.org_Id))   UnitName,
                            getworkshopname(b.org_id) WorkShopName,
                            case when c.level_num=4  then c.Short_Name else null end WorkGroupName,
                            Employee_Name, Identity_CardNo,
                            e.Train_Plan_Name,
                            d.Class_Name,f.Post_Name
                             from zj_train_plan_employee t 
                            inner join Employee b on t.Employee_ID=b.Employee_ID
                            inner join Org c on b.Org_ID=c.Org_ID
                            inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID
                            inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID
                            inner join Post f on b.Post_ID = f.Post_ID
                            where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID");
            OracleAccess db     = new OracleAccess();
            DataSet      ds     = db.RunSqlDataSet(strSql);

            if (ds.Tables[0].Rows.Count == 0)
            {
                Response.Write("<script>alert('没有已上报的员工信息!');window.close();</script>");
                return;
            }

            string       templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm");
            StreamReader reader           = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
            string       html             = reader.ReadToEnd();

            reader.Close();
            Response.Write(html);
            Response.Flush();
            System.Threading.Thread.Sleep(200);

            string jsBlock;

            Excel.Application objApp      = new Excel.ApplicationClass();
            Excel.Workbooks   objbooks    = objApp.Workbooks;
            Excel.Workbook    objbook     = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet   objSheet    = (Excel.Worksheet)objbook.Worksheets[1]; //取得sheet1
            string            filename    = "";
            string            strName     = "";
            string            strFileName = "";

            try
            {
                int count = ds.Tables[0].Rows.Count;
                strName     = "Excel";
                strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员";
                filename    = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls");

                objSheet.Cells.Font.Size = 10;
                objSheet.Cells.Font.Name = "宋体";

                int colIndex = 1;
                objSheet.Cells[1, colIndex] = "序号";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "站段";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "车间";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "班组";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "姓名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "身份证号";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "职名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "上报培训计划名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                colIndex++;
                objSheet.Cells[1, colIndex] = "上报计划培训班名";
                ((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                System.Threading.Thread.Sleep(10);
                jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
                Response.Write(jsBlock);
                Response.Flush();

                int rowIndex = 2;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    colIndex = 1;
                    objSheet.Cells[rowIndex, colIndex] = rowIndex - 1;
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"];
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    colIndex++;
                    objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString();
                    ((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                    System.Threading.Thread.Sleep(10);
                    jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
                    Response.Write(jsBlock);
                    Response.Flush();

                    rowIndex++;
                }

                objSheet.Cells.Columns.AutoFit();

                objApp.Visible = false;

                objbook.Saved = true;
                objbook.SaveCopyAs(filename);

                // 处理完成
                jsBlock = "<script>SetCompleted('处理完成。'); </script>";
                Response.Write(jsBlock);
                Response.Flush();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objbook.Close(Type.Missing, filename, Type.Missing);
                objbooks.Close();
                objApp.Application.Workbooks.Close();
                objApp.Application.Quit();
                objApp.Quit();
                GC.Collect();
            }

            Response.Write("<script>top.returnValue='" + strName + "|" + strFileName + "';window.close();</script>");
        }