コード例 #1
0
ファイル: frmBase.cs プロジェクト: zhenghua75/CMSM
        protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
        {
            try
            {
                Excel.Application xapp   = new Excel.ApplicationClass();
                Excel.Workbook    xbook  = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Worksheet   xSheet = (Excel.Worksheet)xbook.Sheets["业务量"];              //得到Sheet

                xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
                xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
                for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
                {
                    for (int j = 1; j < 8; j++)
                    {
                        xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
                    }
                }

                for (int i = 1; i < 8; i++)
                {
                    xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
                }

                SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
                SaveFileDialog1.Filter   = "Excel文件(*.xls)|*.xls";
                SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
                if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    xbook.SaveCopyAs(SaveFileDialog1.FileName);                                           //另存
                    xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
                    xSheet = null;
                    xbook  = null;
                    xapp.Quit();
                    xapp = null;
                }
                else
                {
                    xbook.Close(false, Missing.Value, Missing.Value);                    //关闭
                    xSheet = null;
                    xbook  = null;
                    xapp.Quit();
                    xapp = null;
                }
            }
            catch (Exception err)
            {
                MessageBox.Show("导出时出错,请重试!", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                clog.WriteLine(err);
            }
            finally
            {
            }
        }
コード例 #2
0
        private void CloseAllSheet()
        {
            try
            {
                workbook1.Close(true, Missing.Value, Missing.Value);
                excelApp1.Quit();
                workbook2.Close(true, Missing.Value, Missing.Value);
                excelApp2.Quit();

                releaseObject(excelApp1);
                releaseObject(excelApp2);
                releaseObject(workbook1);
                releaseObject(workbook2);



                new_workbook.Save();
                new_workbook.Close(true, Missing.Value, Missing.Value);
                newapp.Quit();
                releaseObject(newapp);
                releaseObject(new_workbook);

                Application.Current.Shutdown();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
コード例 #3
0
ファイル: FileHelper.cs プロジェクト: impenitence/Watson
 public static void ConvertExcel(string openPath, string savePath, ref string errText)
 {
     errText = string.Empty;
     try
     {
         //将xml文件转换为标准的Excel格式
         Object            Nothing = Missing.Value;                                                                                                                                                  //由于yongCOM组件很多值需要用Missing.Value代替
         Excel.Application ExclApp = new Excel.ApplicationClass();                                                                                                                                   // 初始化
         Excel.Workbook    ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
         try
         {
             Object format = Excel.XlFileFormat.xlWorkbookNormal;                                                                                                     //获取Excl 2007文件格式
             ExclApp.DisplayAlerts = false;
             ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
         }
         catch (Exception ex)
         {
             errText = ex.Message;
         }
         finally
         {
             ExclDoc.Close(Nothing, Nothing, Nothing);
             ExclApp.Quit();
         }
     }
     catch (Exception ex)
     {
         errText = ex.Message;
     }
 }
コード例 #4
0
        public static DataTable DataTableFromXlsx(string Path, string password = "")
        {
            Excel.Application objXL = null;
            Excel.Workbook    objWB = null;
            objXL = new Excel.Application();
            objWB = objXL.Workbooks.Open(Path);
            Excel.Worksheet objSHT = objWB.Worksheets[1];

            int       rows    = objSHT.UsedRange.Rows.Count;
            int       cols    = objSHT.UsedRange.Columns.Count;
            DataTable dt      = new DataTable();
            int       noofrow = 1;

            for (int c = 1; c <= cols; c++)
            {
                string colname = objSHT.Cells[1, c].Text;
                dt.Columns.Add(colname);
                noofrow = 2;
            }

            for (int r = noofrow; r <= rows; r++)
            {
                DataRow dr = dt.NewRow();
                for (int c = 1; c <= cols; c++)
                {
                    dr[c - 1] = objSHT.Cells[r, c].Text;
                }

                dt.Rows.Add(dr);
            }

            objWB.Close();
            objXL.Quit();
            return(dt);
        }
コード例 #5
0
    public void GenerateReport(IEnumerable <StudentDto> students)
    {
        Excel.Application app = new Excel.Application();
        app.DisplayAlerts = false;
        Excel.Workbook   book_template  = app.Workbooks.Open(@"шаблон_отчета.xlsx");
        Excel._Worksheet sheet_template = book_template.Sheets["отчет"];
        foreach (var ob in students)
        {
            //1. Создаем объкт LearnerReport из БД
            LearnerReport report = new LearnerReport
            {
                SNS         = $"{ob.surname} {ob.name} {ob.SecondName}",
                SchoolName  = ob.SchoolName,
                ClassName   = ob.ClassName,
                TestResult5 = ob.TestResult5
            };
            //2. Экспорт объкта LearnerReport в шаблон xlsx
            sheet_template.Range["C4"].Value2 = report.SNS;
            sheet_template.Range["C5"].Value2 = report.SchoolName;
            sheet_template.Range["C6"].Value2 = report.ClassName;
            sheet_template.Range["C9"].Value2 = report.TestResult5;
            //3. Сохраняем полученный файл в .pdf на рабочем столе
            string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf";
            sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name);
        }

        book_template.Close(0);
        book_template = null;
        app.Quit();
        app = null;
    }
コード例 #6
0
ファイル: OpenKMAddIn.cs プロジェクト: AbdulWarris/dms
 public void checkin(Excel.Workbook activeWorkbook)
 {
     try
     {
         if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
         {
             object saveChanges   = true;
             object missing       = Type.Missing;
             String localFileName = activeWorkbook.FullName;
             activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
             docXML.refresh();                                    // Refresh document list
             if (docXML.isOpenKMDocument(localFileName))
             {
                 OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
                 docXML.remove(oKMDocument);
                 DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
                 if (File.Exists(localFileName))
                 {
                     File.Delete(localFileName);
                 }
             }
         }
     }
     catch (Exception e)
     {
         String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
         MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
     }
 }
コード例 #7
0
        //把Excel文档转换为Pdf文档

        #region
        private static void ExcelToPdf(string ExcelFileName, string PdfFileName)
        {
            //Object missing = System.Reflection.Missing.Value;
            //Object ReadOnly = (object)true;
            //ET.Application app = new ET.Application();
            //app.Visible = false;

            //ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

            //doc.ExportPdf(PdfFileName, "", "");
            //doc.Close(missing, missing, missing);
            //app.Quit();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            //app = null;


            Object missing  = System.Reflection.Missing.Value;
            Object ReadOnly = (object)true;

            Excel.Application app = new Excel.Application();
            app.Visible = false;

            Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

            doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName);
            doc.Close(missing, missing, missing);
            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;
        }
コード例 #8
0
        public void ReadSample()
        {
            Excel.Application excelApp = new Excel.Application();
            if (excelApp != null)
            {
                Excel.Workbook  excelWorkbook  = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];

                Excel.Range excelRange = excelWorksheet.UsedRange;
                int         rowCount   = excelRange.Rows.Count;
                int         colCount   = excelRange.Columns.Count;

                for (int i = 1; i <= rowCount; i++)
                {
                    for (int j = 1; j <= colCount; j++)
                    {
                        Excel.Range range     = (excelWorksheet.Cells[i, 1] as Excel.Range);
                        string      cellValue = range.Value.ToString();

                        //do anything
                    }
                }

                excelWorkbook.Close();
                excelApp.Quit();
            }
        }
コード例 #9
0
ファイル: Program.cs プロジェクト: bicbot/Workbench
        static void Main(string[] args)
        {
            Excel.Application excel        = new Excel.Application();
            string            originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";

            Excel.Workbook  workbook  = excel.Workbooks.Open(originalPath);
            Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
            Excel.Range     usedRange = worksheet.UsedRange;

            // Start test for looping thru each excel worksheet
            Stopwatch sw = new Stopwatch();

            Console.WriteLine("Start stopwatch to loop thru WORKSHEET...");
            sw.Start();
            ConventionalRemoveEmptyRowsCols(worksheet);
            sw.Stop();
            Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");

            string newPath = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruWorksheet.xls";

            workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
            workbook.Close();
            Console.WriteLine("");

            // Start test for looping thru object array
            workbook  = excel.Workbooks.Open(originalPath);
            worksheet = workbook.Worksheets["Sheet1"];
            usedRange = worksheet.UsedRange;
            Console.WriteLine("Start stopwatch to loop thru object array...");
            sw = new Stopwatch();
            sw.Start();
            DeleteEmptyRowsCols(worksheet);
            sw.Stop();

            // display results from second test
            Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
            string newPath2 = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruArray.xls";

            workbook.SaveAs(newPath2, Excel.XlSaveAsAccessMode.xlNoChange);
            workbook.Close();
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            Console.WriteLine("");
            Console.WriteLine("Finished testing methods - Press any key to exit");
            Console.ReadKey();
        }
コード例 #10
0
        public static void PrintPreview(string fileName, PageSetup p)
        {
            Excel.Application excelApp = null;
            Excel.Workbook    workBook = null;
            try
            {
                object objOpt = System.Reflection.Missing.Value;
                excelApp = ExcelInit();
                workBook = excelApp.Workbooks.Open(fileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt,
                                                   objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);

                if (p != null)
                {
                    //if (p.TopMargin != 0 || p.LeftMargin != 0 || p.RightMargin != 0 || p.BottomMargin != 0)
                    {
                        int             sheetCount = workBook.Sheets.Count;
                        Excel.Worksheet workSheet  = null;
                        int             i          = 0;
                        while (i <= sheetCount)
                        {
                            try
                            {
                                workSheet = workBook.Sheets[i];
                                break;
                            }
                            catch
                            {
                                i++;
                            }
                        }

                        workSheet.PageSetup.LeftMargin   = p.GetLeftMargin();
                        workSheet.PageSetup.RightMargin  = p.GetRightMargin();
                        workSheet.PageSetup.TopMargin    = p.GetTopMargin();
                        workSheet.PageSetup.BottomMargin = p.GetBottomMargin();
                        //workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
                    }
                }

                excelApp.Visible = true;
                workBook.PrintPreview(objOpt);
                excelApp.Visible = false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (workBook != null)
                {
                    workBook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                ExcelTerminal(excelApp);
            }
        }
コード例 #11
0
        public static void getExcelFile()
        {
            //Create COM Objects. Create a COM object for everything that is referenced
            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx");
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            //IMPORTANT SECTION
            var dictionary = new Dictionary <string, List <string> >();

            //iterate over the rows and columns as it appears in the file
            //excel is not zero based!!
            for (int i = 1; i <= rowCount; i++)
            {
                //it would be nice if we add some null checking to this variables. Check the article again
                var col1 = xlRange.Cells[i, 1].Value2.ToString();
                var col2 = xlRange.Cells[i, 2].Value2.ToString();

                if (dictionary.ContainsKey(col1))
                {
                    var existingList = dictionary[col1];
                    existingList.Add(col2);
                }
                else
                {
                    var newList = new List <string>();
                    newList.Add(col2);
                    dictionary.Add(col1, newList);
                }
            }
            //Do whatever you'd like with the dictionary
            //END OF IMPORTANT SECTION
            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //rule of thumb for releasing com objects:
            //  never use two dots, all COM objects must be referenced and released individually
            //  ex: [somthing].[something].[something] is bad

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();


            Marshal.ReleaseComObject(xlApp);
        }
コード例 #12
0
        private void ReadExcelFile()
        {
            MyBook  = MyApp.Workbooks.Open(Environment.CurrentDirectory + "\\" + name + ".csv");
            MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
            int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
            int close = 7, date = 1;
            int offset = DAYS_OFFSET - 2;

            System.Array HistoricalData = (System.Array)MySheet.get_Range("A2", "G" + lastRow.ToString()).Cells.Value;

            /*Console.WriteLine("contenu du fichier : " + MyBook.Name + " : ");
             * for (int i = 1; i <= HistoricalData.Length; i++)
             * {
             *  Console.Write(HistoricalData.GetValue(i, 1));
             *  Console.Write(" : ");
             *  Console.WriteLine(HistoricalData.GetValue(i, 7));
             * }*/
            DateTime theoriticalDate = datesAndValuation.ElementAt(0).Key, currentDate;

            datesAndValuation.Remove(theoriticalDate);
            double value = 0, Pi = 0, PiMinusOne = 100;
            //  List<double> realValues = new List<double>();

            int i = lastRow - 1;

            do
            {
                currentDate = DateTime.Parse(HistoricalData.GetValue(i, date).ToString());
                if (i == lastRow - 1)
                {
                    theoriticalDate = currentDate;
                }
                while (theoriticalDate < currentDate)
                {
                    i++;
                    currentDate = DateTime.Parse(HistoricalData.GetValue(i, date).ToString());
                }
                // value= double.Parse(HistoricalData.GetValue(i, close).ToString());
                Pi         = (double.Parse(HistoricalData.GetValue(i, close).ToString()));
                PiMinusOne = (i == lastRow - 1 ? 100 : realValues.ElementAt(realValues.Count() - 1));
                realValues.Add(double.Parse(HistoricalData.GetValue(i, close).ToString()));
                value = (i == lastRow - 1) ? (100) : (100 * Pi / PiMinusOne);

                // USING THE THEORITICAL DATE
                //datesAndValuation.Add(theoriticalDate, value);
                // USING THE REAL DATE
                datesAndValuation.Add(currentDate, value);
                i -= offset;
                theoriticalDate = theoriticalDate.AddDays(DAYS_OFFSET);
            }while (i >= 1);

            MyBook.Close();
            Console.WriteLine(name + " " + datesAndValuation.Count() + "pair of values : ");
        }
コード例 #13
0
 /// <summary>
 /// 关闭Excel内存进程
 /// </summary>
 /// <param name="xlsApp">应用程序</param>
 /// <param name="xlsWorkbook">工作簿</param>
 /// <param name="xlsSheet">工作表</param>
 public static void CloseXls(Excel.Application xlsApp, Excel.Workbook xlsWorkbook, Excel.Worksheet xlsSheet)
 {
     xlsWorkbook.Close();
     xlsApp.Workbooks.Close();
     xlsApp.Quit();
     //关闭EXCEL进程
     System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
     System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkbook);
     System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
     xlsWorkbook = null;
     xlsApp      = null;
 }
コード例 #14
0
ファイル: InitConnect.cs プロジェクト: a1264393659/book
 /// <summary>
 /// 导出数据
 /// </summary>
 /// <param name="selectstring">选择导出数据的SQL选择语句</param>
 public static void DataOut(SaveFileDialog dialog, string selectstring)
 {
     if (dialog.ShowDialog() == DialogResult.OK)
     {
         Excel.ApplicationClass excel     = new Excel.ApplicationClass();
         Excel.Workbook         workbook  = excel.Workbooks.Add(System.Reflection.Missing.Value);
         Excel.Worksheet        worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
         worksheet.Cells.NumberFormatLocal = "@";
         ///////////////////////////////////
         SqlConnection connect = InitConnect.GetConnection();
         SqlDataReader read    = null;
         try
         {
             connect.Open();
             SqlCommand cmd = new SqlCommand(selectstring, connect);
             read = cmd.ExecuteReader();
             for (int i = 0; i < read.FieldCount; i++)
             {
                 worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
             }
             int row   = 2;
             int count = 0;
             while (read.Read())
             {
                 count++;
                 for (int i = 0; i < read.FieldCount; i++)
                 {
                     worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
                 }
                 row++;
             }
             MessageBox.Show("成功导出" + count.ToString() + "条记录!", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
         }
         catch (Exception ee)
         {
             MessageBox.Show("错误:" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
         }
         finally
         {
             read.Close();
             connect.Close();
             object change = false, filename = dialog.FileName;
             workbook.SaveCopyAs(filename);
             workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
             excel.Quit();
         }
     }
 }
コード例 #15
0
ファイル: FeedsController.cs プロジェクト: SCHarish/Hackathon
        private string ExportDataSetToExcel(DataSet ds, string location)
        {
            //Creae an Excel application instance
            Excel.Application excelApp = new Excel.Application();
            //string date = DateTime.Now.ToString();

            string loc = location;
            //Path.GetFullPath(loc);
            //   string path = date.Trim();
            int    randomnnumber = rnd.Next(1, 1000);
            string date          = DateTime.Now.ToString("yyyyMMddHHmmss");
            string myPath        = @loc + randomnnumber + date + ".xls";

            //Create an Excel workbook instance and open it from the predefined location
            //  Excel.Workbook excel1 = excelApp.Workbooks.Add(myPath);
            Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();

            foreach (DataTable table in ds.Tables)
            {
                //Add a new worksheet to workbook with the Datatable name
                Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;

                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }

                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                    }
                }
            }
            try
            {
                excelWorkBook.SaveAs(myPath);
                excelWorkBook.Close();
                excelApp.Quit();
            }
            catch (Exception e)
            {
                return("0");
            }
            return("1");
        }
コード例 #16
0
        public static StringDictionary GetTestData()
        {
            StringDictionary dataFromExcel = new StringDictionary();

            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = false;
            Excel.Workbook  xlWb   = xlApp.Workbooks.Open(@"C:\Users\...\Desktop\1.xlsx");
            Excel.Worksheet xlSht1 = xlWb.Worksheets.get_Item(2);
            dataFromExcel.Add("firstName", xlSht1.Cells[2, 1].value);
            dataFromExcel.Add("lastName", xlSht1.Cells[2, 2].value);
            dataFromExcel.Add("gender", xlSht1.Cells[2, 3].value);
            dataFromExcel.Add("tool", xlSht1.Cells[2, 4].value);
            dataFromExcel.Add("continent", xlSht1.Cells[2, 5].value);
            xlWb.Close();
            xlApp.Quit();
            return(dataFromExcel);
        }
コード例 #17
0
    public DataTable GetSheetDataAsDataTable(String filePath, String sheetName)
    {
        DataTable dt = new DataTable();

        try
        {
            xlApp   = new Excel.Application();
            xlBook  = xlApp.Workbooks.Open(filePath);
            xlSheet = xlBook.Worksheets[sheetName];
            xlRange = xlSheet.UsedRange;
            DataRow row = null;
            for (int i = 1; i <= xlRange.Rows.Count; i++)
            {
                if (i != 1)
                {
                    row = dt.NewRow();
                }
                for (int j = 1; j <= xlRange.Columns.Count; j++)
                {
                    if (i == 1)
                    {
                        dt.Columns.Add(xlRange.Cells[1, j].value);
                    }
                    else
                    {
                        row[j - 1] = xlRange.Cells[i, j].value;
                    }
                }
                if (row != null)
                {
                    dt.Rows.Add(row);
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            xlBook.Close();
            xlApp.Quit();
        }
        return(dt);
    }
コード例 #18
0
 public ExcelControl()
 {
     // This call is required by the Windows.Forms Form Designer.
     InitializeComponent();
     // TODO: Add any initialization after the InitComponent call
     try
     {
         excelApp         = new Excel.ApplicationClass();
         excelApp.Visible = true;
         Excel.Workbook book = excelApp.Workbooks.Open(@"C:\Documents and Settings\raghunandanr\Desktop\Benefit Design Modeler V3.28_ Blank.xls", Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
         this.CreateGraphics().DrawString("ok ok", new System.Drawing.Font("Arial", 25), Brushes.GreenYellow, 10, 10);
         book.Close(false, Type.Missing, Type.Missing);
     }
     catch (Exception ex)
     {
         this.CreateGraphics().DrawString(ex.Message, new System.Drawing.Font("Arial", 16), Brushes.Chocolate, 10, 10);
     }
 }
コード例 #19
0
 public void closeExcel()
 {
     try
     {
         myExcelWorkbook.SaveAs(excelFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
                                System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                System.Reflection.Missing.Value, System.Reflection.Missing.Value); // Save data in excel
         myExcelWorkbook.Close(true, excelFilePath, System.Reflection.Missing.Value);              // close the worksheet
     }
     finally
     {
         if (myExcelApplication != null)
         {
             myExcelApplication.Quit();     // close the excel application
         }
     }
 }
コード例 #20
0
    private void Form1_Load(object sender, EventArgs e)
    {
        Excel.Application objExcel     = new Excel.Application();
        Excel.Workbook    objWorkbook  = objExcel.Workbooks.Open(@"C:\Users\John\Desktop\cs\TestExcel2_3000.xlsx");
        Excel.Worksheet   objWorksheet = objWorkbook.Worksheets["Tabelle1"];

        zip = ReadSheet(objWorksheet);
        objWorkbook.Close();
        Excel.Workbook  objWorkbook1  = objExcel.Workbooks.Open(@"C:\Users\John\Desktop\cs\TestExcel3_3000.xlsx");
        Excel.Worksheet objWorksheet1 = objWorkbook1.Worksheets["Tabelle1"];

        place = ReadSheet(objWorksheet1);
        objWorkbook1.Close();
        objExcel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook1);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
    }
コード例 #21
0
    public ExcelFile()
    {
        StringBuilder sb = new StringBuilder();

        WB         = xl.Workbooks.Open(Path);
        xl.Visible = true;
        foreach (Excel.Worksheet CurrentWS in WB.Worksheets)
        {
            rng = CurrentWS.UsedRange;
            for (int i = 1; i < rng.Count; i++)
            {
                sb.append(rng.Cells[i].Value);
            }
        }
        FullText = sb.ToString();
        WB.Close(false);
        xl.Quit();
    }
コード例 #22
0
    static void Main(string[] args)
    {
        Excel.Application excel        = new Excel.Application();
        string            desktopPath  = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        string            originalPath = desktopPath + @"\ExcelRemove\Book1_Test.xls";

        Excel.Workbook  workbook  = excel.Workbooks.Open(originalPath);
        Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
        DeleteEmptyRowsCols(worksheet);
        string newPath = desktopPath + @"\ExcelRemove\Book1_Test_Removed.xls";

        workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
        workbook.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
        Console.ReadKey();
    }
コード例 #23
0
ファイル: excel_operator.cs プロジェクト: qxuan521/Coding
 private static void excelSaveClose(string szPath, string szResultPath, Excel.Application rExcel, Excel.Workbook rWorkbook)
 {
     Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
     if (localByNameApp.Length > 0)
     {
         foreach (var app in localByNameApp)
         {
             //                  if (!app.HasExited)
             //                  {
             #region
             ////设置禁止弹出保存和覆盖的询问提示框
             rExcel.DisplayAlerts          = false;
             rExcel.AlertBeforeOverwriting = false;
             rExcel.Visible = false;
             //wb.Saved = true;
             ////保存工作簿
             //rExcel.Application.Workbooks.Add(true).Save();
             //保存excel文件
             ///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
             //确保Excel进程关闭
             rExcel.Quit();
             rExcel = null;
             #endregion
             app.Kill();//关闭进程
             /*}*/
         }
     }
     if (rWorkbook != null)
     {
         rExcel.DisplayAlerts          = false;
         rExcel.AlertBeforeOverwriting = false;
         rExcel.Visible = false;
         //rExcel.Application.Workbooks.Add(true).Save();
         //保存结果
         rWorkbook.SaveCopyAs(szResultPath);
         ///*, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing*/);
         //wb.Save();
         rWorkbook.Close(false, szPath, szPath);
     }
     rExcel.Quit();
     // 安全回收进程
     System.GC.GetGeneration(rExcel);
 }
コード例 #24
0
        /// <summary>
        /// 保存--查询结果
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStructSave_Click(object sender, EventArgs e)
        {
            //1.打开Excle程序
            Excel.Application application = new Excel.Application();
            application.Visible     = false;
            application.UserControl = false;
            Excel.Workbook  workBook = (Excel.Workbook)(application.Workbooks.Add(Type.Missing));
            Excel.Worksheet mySheet  = workBook.Sheets[1] as Excel.Worksheet; //第一个sheet页

            //2.遍历产品结构树
            getchildValue(this.list_productTreeStruct.Items[0], mySheet, 0, 0);

            //3.关闭Excle程序
            workBook.Close(true, Type.Missing, Type.Missing);
            workBook = null;
            application.Quit();
            application = null;
            MessageBox.Show("保存成功");
        }
コード例 #25
0
        /// <summary>
        /// Closes the currently opened spreadsheet
        /// </summary>
        public void CloseSpreadsheet()
        {
            try
            {
                // Repeat xl.Visible and xl.UserControl releases just to be sure
                // we didn't error out ahead of time.

                if (ExcelObj != null)
                {
                    ExcelObj.Visible     = false;
                    ExcelObj.UserControl = false;
                } // end if

                if (theWorkbook != null)
                {
                    // Close the document and avoid user prompts to save if our method failed.
                    theWorkbook.Close(false, null, null);
                    ExcelObj.Workbooks.Close();
                } // end if
            }
            catch { }

            // Gracefully exit out and destroy all COM objects to avoid hanging instances
            // of Excel.exe whether our method failed or not.

            if (theWorkbook != null)
            {
                Marshal.ReleaseComObject(theWorkbook);
            }
            if (ExcelObj != null)
            {
                ExcelObj.Quit();
            }
            if (ExcelObj != null)
            {
                Marshal.ReleaseComObject(ExcelObj);
            }

            theWorkbook = null;
            ExcelObj    = null;
            GC.Collect();
        }
コード例 #26
0
        private Boolean xls_exec(OfficeDomain office)
        {
            Boolean result = false;

            if (Array.IndexOf(xls, office.prefix) > -1)
            {
                log("excel开始工作...");
                Excel.Workbook workbook = null;
                try
                {
                    //参数含义:路径、只读
                    workbook = excelApp.Workbooks.Open(office.path, 0, true);
                    //转换
                    workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, office.pdfFileName);
                    result = true;
                    log("excel工作完毕,准备关闭文件..");
                }
                catch (Exception e)
                {
                    error("excel工作异常:{0}", e.Message);
                }
                finally
                {
                    if (workbook != null)
                    {
                        try
                        {
                            workbook.Close();
                            log("excel文件成功关闭..");
                        }
                        catch (Exception e)
                        {
                            error("excel文件关闭时发生错误{1},重启{0}.exe", xls_name, e.Message);
                            xls_start();
                        }
                    }
                }
            }
            return(result);
        }
コード例 #27
0
        private void ExportDataSetToExcel(DataSet ds)
        {
            //Creae an Excel application instance
            Excel.Application excelApp = new Excel.Application();
            //string date = DateTime.Now.ToString();
            string date = DateTime.Now.ToString("yyyy-mm-dd");
            string loc  = ConfigurationManager.AppSettings["location"];
            string path = date.Trim();

            string myPath = @loc + date + ".xls";

            //Create an Excel workbook instance and open it from the predefined location
            //  Excel.Workbook excel1 = excelApp.Workbooks.Add(myPath);
            Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();

            foreach (DataTable table in ds.Tables)
            {
                //Add a new worksheet to workbook with the Datatable name
                Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;

                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }

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

            excelWorkBook.SaveAs(myPath);
            excelWorkBook.Close();
            excelApp.Quit();
        }
コード例 #28
0
    static void Main(string[] args)
    {
        Excel.Application excel        = new Excel.Application();
        string            originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";

        Excel.Workbook  workbook  = excel.Workbooks.Open(originalPath);
        Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
        Excel.Range     usedRange = worksheet.UsedRange;

        RemoveEmptyTopRowsAndLeftCols(worksheet, usedRange);
        DeleteEmptyRowsCols(worksheet);

        string newPath = @"H:\ExcelTestFolder\Book1_Test_Removed.xls";

        workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
        workbook.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
        Console.ReadKey();
    }
コード例 #29
0
    public Tyburn1()
    {
        Excel.Application oXL = new Excel.Application();
        oXL.Visible = false;
        Excel.Workbook  oWB    = oXL.Workbooks.Add(missing);
        Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
        oSheet.Name        = "The first sheet";
        oSheet.Cells[1, 1] = "Something";
        Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
                                  as Excel.Worksheet;
        oSheet2.Name        = "The second sheet";
        oSheet2.Cells[1, 1] = "Something completely different";
        string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
                          + "\\SoSample.xlsx";

        oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                   missing, missing, missing, missing,
                   Excel.XlSaveAsAccessMode.xlNoChange,
                   missing, missing, missing, missing, missing);
        oWB.Close(missing, missing, missing);
        oXL.UserControl = true;
        oXL.Quit();
    }
コード例 #30
0
        private void CreateExcel()
        {
            try
            {
                xlApp   = new Excel.Application();
                xlWB    = xlApp.Workbooks.Add(Missing.Value);
                xlSheet = xlWB.ActiveSheet;
                CreateTable();
                xlApp.Visible     = true;
                xlApp.UserControl = true;
            }
            catch (Exception ex)
            {
                string errMsg = string.Format("Error: {0}\nLine: {1}", ex.Message, ex.Source);
                MessageBox.Show(errMsg, "Error");


                xlWB.Close(false, Type.Missing, Type.Missing);
                xlApp.Quit();
                xlWB  = null;
                xlApp = null;
            }
        }