Пример #1
1
 private void ExportarDataGridViewExcel(DataGridView grd)
 {
     SaveFileDialog fichero = new SaveFileDialog();
     fichero.Filter = "Excel (*.xls)|*.xls";
     if (fichero.ShowDialog() == DialogResult.OK)
     {
         Microsoft.Office.Interop.Excel.Application aplicacion;
         Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
         Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
         aplicacion = new Microsoft.Office.Interop.Excel.Application();
         libros_trabajo = aplicacion.Workbooks.Add();
         hoja_trabajo =
             (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
         //Recorremos el DataGridView rellenando la hoja de trabajo
         for (int i = 0; i < grd.Rows.Count ; i++)
         {
             for (int j = 0; j < grd.Columns.Count; j++)
             {
                 hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString();
             }
         }
         libros_trabajo.SaveAs(fichero.FileName,
             Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
         libros_trabajo.Close(true);
         aplicacion.Quit();
     }
 }
Пример #2
1
        private void dataGridViewExportAsExcel(DataGridView dgv, string FileFullPath)
        {
            string strExamPath = FileFullPath.Substring(0, FileFullPath.LastIndexOf('\\'));
            if (!System.IO.Directory.Exists(strExamPath))
            {
                MessageBox.Show(FileFullPath, "目录错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
            excelApplication.EnableEvents = false;
            excelApplication.Application.DisplayAlerts = false;
            excelApplication.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
            excelApplication.Visible = false;
            int nRowIndex    = 0;
            int nColumnIndex = 0;
            object[,] strArr = new object[gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount];
            foreach (DataGridViewColumn dgvc in dgv.Columns)
            {
                strArr[nRowIndex, nColumnIndex] = dgvc.HeaderText;
                ++nColumnIndex;
            }
            ++nRowIndex;
            nColumnIndex = 0;
           
            foreach (DataGridViewRow dgvr in dgv.Rows)
            {
                foreach (DataGridViewCell dgvcell in dgvr.Cells)
                {
                    strArr[nRowIndex, nColumnIndex] = dgvcell.Value.ToString();
                    ++nColumnIndex;                    
                }
                ++nRowIndex;
                nColumnIndex = 0;
            }
            
            string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(dgv.ColumnCount, dgv.RowCount + 1);
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1",strExcelMaxColumnIndex);
            myRange.get_Resize(dgv.RowCount + 1, dgv.ColumnCount);
            try
            {
                myRange.Value2 = strArr;                
                myRange.Columns.AutoFit();                
                myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;                

                myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
            MessageBox.Show("文件成功保存到了" + FileFullPath, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

            excelApplication.Quit();            
            killexcel(excelApplication);            
            GC.Collect();
        }
Пример #3
1
       /// <summary>
       /// 导出Excel表格文件
       /// </summary>
 
       public static void ExportExcelFile(DataTable ExcelTable)
       {
           try
           {
               string SaveExcelName = string.Empty;//保存的Excel文件名称
               SaveFileDialog SFDialog = new SaveFileDialog();
               SFDialog.DefaultExt = "xlsx";
               SFDialog.Filter = "Excel文件(*.xlsx;*.xls)|*.xlsx;*.xls";
               SFDialog.ShowDialog();
               SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
               if (SaveExcelName.IndexOf(":") < 0) return ;
               Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
               object missing = System.Reflection.Missing.Value;
               if (XlsApp == null)
               {
                   MessageBoxEx.Show("无法创建Excel表格文件,您的电脑可能未安装Excel软件!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                   return ;
               }
               else
               {

                   Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
                   Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
                   Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
                   Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
                   int TotalCount = ExcelTable.Rows.Count;
                   int rowRead = 0;//读取行数
                   float PercentRead = 0;//导入百分比
                   //写入字段名
                   for (int i = 0; i < ExcelTable.Columns.Count;i++ )
                   {
                       WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
                       Ran=(Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1,i+1];//列名称写入单元格
                       Ran.Interior.ColorIndex = 15;
                       Ran.Font.Bold = true;//标题加粗

                   }
                   ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
                   ProgressBarMsg.StepMaxValue = TotalCount;//获取进度条最大范围
                   ProgBarMsg.ShowDialog();//显示进度条
                   string ProgressPercent = string.Empty;//进度百分比
                   //写字段值
                   for (int j = 0; j < ExcelTable.Rows.Count; j++)
                   {
                       for (int k = 0; k < ExcelTable.Columns.Count; k++)
                       {
                           WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
                       }
                       rowRead++;
                       PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
                       ProgressPercent = PercentRead.ToString("0.00") + "%";
                       ProgressBarMsg.ProgressValue = ProgressPercent;//获取进度百分比
                       ProgressBarMsg.CurrentValue = rowRead;//获取当前进度值
                       Application.DoEvents();//处理当前在消息队列中所有windows消息
                   }            

                   WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing,missing);
                   Ran = WkSheet.get_Range(WkSheet.Cells[2, 1],WkSheet.Cells[ExcelTable.Rows.Count + 2,ExcelTable.Columns.Count]);//给工作表指定区域
                   //设置Excel表格边框样式
                   Ran.BorderAround2(missing,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
                   Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,missing,missing);
                   Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
                   Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
                   Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度

                   if (ExcelTable.Columns.Count > 1)
                   {//设置垂直表格颜色索引
                     Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                   }
                   //关闭表格对像,并退出应用程序域
                   WkBk.Close(missing,missing,missing);
                   XlsApp.Quit();
                   ProgBarMsg.Close();//关闭进度条 

               }

               
               
           }catch(Exception ex)
           {
               MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);

               
           }
       }
Пример #4
0
        public void Print(YellowstonePathology.Business.Search.ReportSearchList caseList, string description, DateTime printDate)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = false;

            Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(@"\\CFileServer\documents\ReportTemplates\MolecularTesting\CaseList.xlt");
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

            ws.Cells[3, 1] = "Batch: " + description + " - " + printDate.ToShortDateString();

            int rowPosition = 6;

            for (int i = caseList.Count - 1; i > -1; i--)
            {
                ws.Cells[rowPosition, 1] = caseList[i].ReportNo;
                ws.Cells[rowPosition, 2] = caseList[i].PanelSetName;
                ws.Cells[rowPosition, 3] = caseList[i].PatientName;
                ws.Cells[rowPosition, 4] = caseList[i].PhysicianName + " - " + caseList[i].ClientName;
                ws.Cells[rowPosition, 5] = caseList[i].OrderedBy;
                rowPosition++;
            }

            Object oMissing = Type.Missing;
            Object oFalse = false;

            ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.Close(oFalse, oMissing, oMissing);
            xlApp.Quit();
        }
        public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel()
        {
            StringBuilder content = new StringBuilder();
            content.AppendLine("param1\tparam2\tstatus");
            content.AppendLine("0.5\t10\tpassed");
            content.AppendLine("10\t20\tfail");

            using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
            {
                Clipboard.SetText(content.ToString());
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Microsoft.Office.Interop.Excel.Application();
                // for excel visibility
                //xlexcel.Visible = true;
                // Creating a new workbook
                xlWorkBook = xlexcel.Workbooks.Add(misValue);
                // Putting Sheet 1 as the sheet you want to put the data within
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
                // creating the range
                Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1];
                CR.Select();
                xlWorkSheet.Paste(CR, false);
                xlWorkSheet.SaveAs(xlsxFile.FileName);                
                xlexcel.Quit();
                Console.WriteLine("Created file {0}", xlsxFile.FileName);
            }
        }
        public static void OpenExcelDocs2(string filename, double[] content)
        {

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.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);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;

            sheet.Cells[24, 3] = content[1];
            sheet.Cells[25, 3] = content[0];

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();  //应用程序推出,但是进程还在运行

            IntPtr t = new IntPtr(excel.Hwnd);          //杀死进程的好方法,很有效
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            //sheet = null;
            //book = null;
            //excel = null;   //不能杀死进程

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);  //可以释放对象,但是不能杀死进程
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);


        }
Пример #7
0
        public void insertBTN(String query, String path)
        {
            sqlCon.ConnectionString = conn;
            sqlCon.Open();

            SqlDataAdapter da = new SqlDataAdapter(query, sqlCon);
            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
            da.Fill(dtMainSQLData);
            DataColumnCollection dcCollection = dtMainSQLData.Columns;

            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.Application ExcelApp = new
            Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);

            // ExcelApp.Cells.CopyFromRecordset(objRS);

            for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
            {

                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                    {
                        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                    }
                    else
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                }
            }

            ExcelApp.ActiveWorkbook.SaveCopyAs(path + "\\Results.xlsx");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
        }
Пример #8
0
        public List<string> GetExcelSheets(string excelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
            workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "",
            true,
            false,
            0,
            true,
            false,
            false);
            Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
            // get the first and only worksheet from the collection of worksheets
            List<string> sheetNames = new List<string>();
            Regex regSheetName = new Regex("Sheet\\d+");
            foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) {
                if (!regSheetName.IsMatch(sheet.Name)) {
                    sheetNames.Add(sheet.Name);
                }
                Marshal.ReleaseComObject(sheet);
            }

            excelFileObject.Quit();
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(workBookObject);
            Marshal.ReleaseComObject(excelFileObject);
            return sheetNames;
        }
 /// <summary>
 /// Закрыть приложение
 /// </summary>
 public void CloseApplication()
 {
     _applicationWord?.Quit();
     _applicationExcel?.Application.Quit();
     _applicationExcel?.Quit();
     _applicationWord  = null;
     _applicationExcel = null;
     ExcelInstance.KillAllPreviousProcess();
 }
Пример #10
0
        public string exportData(String query)
        {
            string conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            SqlConnection sqlConn = new SqlConnection();
            sqlConn.ConnectionString = conn;
            sqlConn.Open();

            SqlDataAdapter da = new SqlDataAdapter(query, sqlConn);
            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
            da.Fill(dtMainSQLData);
            DataColumnCollection dcCollection = dtMainSQLData.Columns;

            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.Application ExcelApp = new
            Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);

            // ExcelApp.Cells.CopyFromRecordset(objRS);

            for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
            {

                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                    {
                        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                    }
                    else
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                }
            }

            string path = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)).FullName;
            if (Environment.OSVersion.Version.Major >= 6)
            {
                path = Directory.GetParent(path).ToString();
            }

            //This path belongs to the computer where the application is running.
            //I have to find a way to get the path from the client side...

            try
            {
                ExcelApp.ActiveWorkbook.SaveAs(path + "\\Desktop\\Results.xlsx");
                ExcelApp.ActiveWorkbook.Saved = true;

                ExcelApp.Quit();

                return "The file has been generated successfully and it is ready on your Desktop.";
            }
            catch (Exception e)
            {
                return "Some error has occured. Message from Server: " + e.Message;
            }
        }
Пример #11
0
        /*
         * Modify by ManhTV3 on 27/04/2012
         * Kết xuất file excel báo cáo tiến độ chuẩn hóa
         * */
        public static void Prc_Bcao_Chuan_Hoa(string p_sourcePath, 
            string p_destinPath)
        {
            Microsoft.Office.Interop.Excel.Application _excelApp;
            _excelApp = new Microsoft.Office.Interop.Excel.Application();

            // Mở file mẫu excel
            Microsoft.Office.Interop.Excel.Workbook workBook =
                _excelApp.Workbooks.Open(p_sourcePath,  //Filename
                                         Type.Missing,  //UpdateLinks
                                         Type.Missing,  //ReadOnly
                                         Type.Missing,  //Format
                                         Type.Missing,  //Password
                                         Type.Missing,  //WriteResPassword
                                         Type.Missing,  //IgnoreReadOnlyRecommended
                                         Type.Missing,  //Origin
                                         Type.Missing,  //Delimiter
                                         Type.Missing,  //Editable
                                         Type.Missing,  //Notify
                                         Type.Missing,  //Converter
                                         Type.Missing,  //AddToMru
                                         Type.Missing,  //Local
                                         Type.Missing); //CorruptLoad
            // Lấy dữ liệu
            using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ))
            {
                try
                {
                    _ora.TransStart();
                    // Kết xuất báo cáo tiến độ chuẩn hóa
                    string _sql = "SELECT * FROM vw_bc_ch;";
                    DataTable _dt = _ora.TransExecute_DataTable(_sql);
                    if (_dt.Rows.Count > 0)
                        CLS_EXCEL.Prc_Add_Sheets(workBook, "BaoCaoChuanHoa", _dt);
                    _dt.Clear();

                    workBook.SaveAs(p_destinPath,
                                        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);
                    //_ora.TransCommit();
                }
                finally
                {
                    // Đóng file mẫu excel
                    workBook.Close(false, p_sourcePath, null);
                    _excelApp.Quit();

                    CLS_EXCEL.Prc_releaseObject(workBook);
                    CLS_EXCEL.Prc_releaseObject(_excelApp);
                }
            }
        }
Пример #12
0
        public static string ExpExcel(string path)
        {
            ReturnDoc obj_ReturnDoc = new ReturnDoc();
            try
            {

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            worksheet.Cells[1, 1] = "管道名称";

            // 数据查询

            OleDbDataReader rst = null;
            string ret = CommonQuery.qryRst("select * from PIPELINE", ref rst);
            Int16 i = 2;

            DicCache dic = DicCache.getInstance();

            if (ret.Equals("0"))
            {
                while (rst.Read())
                {
                    worksheet.Cells[i, 1] = rst["DATA1"].ToString();
                    i++;
                }

                rst.Close();
            }

            string sPath = path;
            string filename = "海底管道数据导出.xls";

            workbook.Saved = true;
            workbook.SaveCopyAs(sPath + filename);

            xlApp.Quit();

            obj_ReturnDoc.addErrorResult(Common.RT_SUCCESS);
            obj_ReturnDoc.setFuncErrorInfo(filename);

            }
            catch (Exception e)
            {
            obj_ReturnDoc.addErrorResult(Common.RT_FUNCERROR);
            obj_ReturnDoc.setFuncErrorInfo(e.Message);
            }

            return obj_ReturnDoc.getXml();
        }
Пример #13
0
 public void ExportExcel(string fileName, DataGridView myDGV)
 {
     string saveFileName = "";
     SaveFileDialog saveDialog = new SaveFileDialog();
     saveDialog.DefaultExt = "xls";
     saveDialog.Filter = "Excel文件|*.xls";
     saveDialog.FileName = fileName;
     saveDialog.ShowDialog();
     saveFileName = saveDialog.FileName;
     if (saveFileName.IndexOf(":") < 0) return; //被点了取消
     Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
     if (xlApp == null)
     {
         MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
         return;
     }
     Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
     Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
     Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                                                                                                                           //写入标题
     for (int i = 0; i < myDGV.ColumnCount; i++)
     {
         worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
     }
     //写入数值
     for (int r = 0; r < myDGV.Rows.Count; r++)
     {
         for (int i = 0; i < myDGV.ColumnCount; i++)
         {
             worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
         }
         System.Windows.Forms.Application.DoEvents();
     }
     worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
     if (saveFileName != "")
     {
         try
         {
             workbook.Saved = true;
             workbook.SaveCopyAs(saveFileName);
         }
         catch (Exception ex)
         {
             MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
         }
     }
     xlApp.Quit();
     GC.Collect();//强行销毁
     MessageBox.Show("文件: " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }
        public static void toXLSX(string filePath, string destination)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(filePath, 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);

            // this does not throw exception if file doesnt exist
            File.Delete(destination);

            wb.SaveAs(destination, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, Type.Missing);

            wb.Close(false, Type.Missing, Type.Missing);

            app.Quit();
        }
Пример #15
0
        public static FileInfo Export(ExcelPdfExportSettings exportSettings)
        {
            if (exportSettings == null) throw new ArgumentNullException("exportSettings");
            if (exportSettings.OutputPdfPath.IsNullOrBlank()) throw new ArgumentException("OutputPdfPath is required.");

            if (File.Exists(exportSettings.OutputPdfPath))
            {
                File.Delete(exportSettings.OutputPdfPath);
            }

            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application() { ScreenUpdating = false, DisplayAlerts = false }; ;
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;

            try
            {
                // Create new instance of Excel and open Workbook
                excelWorkbook = excelApplication.Workbooks.Open(exportSettings.WorkbookPath);
                if (excelWorkbook == null)
                {
                    throw new ApplicationException(String.Format("Specified Workbook '{0}' could not be found or may be corrupt.", exportSettings.WorkbookPath));
                }

                // Get Active sheet and Range to export to Pdf
                var sheets = excelWorkbook.Sheets.OfType<Microsoft.Office.Interop.Excel.Worksheet>();
                Microsoft.Office.Interop.Excel.Worksheet worksheet = sheets.FirstOrDefault(s => s.Name.Equals(exportSettings.WorksheetName, StringComparison.OrdinalIgnoreCase));
                if (worksheet == null)
                {
                    throw new ApplicationException(String.Format("Specified Workbook Sheet '{0}' not found.", exportSettings.WorksheetName));
                }

                Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(exportSettings.WorksheetRange);
                range.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, exportSettings.OutputPdfPath);

                return new FileInfo(exportSettings.OutputPdfPath);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (excelWorkbook != null)
                { excelWorkbook.Close(); }
                excelApplication.Quit();
                excelApplication = null;
                excelWorkbook = null;
            }
        }
Пример #16
0
        /// <summary>
        /// 根据
        /// </summary>
        /// <param name="contractFilePath"></param>
        /// <returns></returns>
        public static IEnumerable<Contract> GetContracts(string contractFilePath)
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = app.Workbooks.Open(contractFilePath, 0,
                                                              false, 5, System.Reflection.Missing.Value,
                                                              System.Reflection.Missing.Value,
                                                              false, System.Reflection.Missing.Value,
                                                              System.Reflection.Missing.Value, true, false,
                                                              System.Reflection.Missing.Value, false, false, false);

            // 
            //app.Visible = true;
            var result = ContractCollector.GetContracts(app);
            app.Quit();
            return result;
        }
Пример #17
0
        public void inp(Form1 f)
        {
            string str, filename="";
            int rCnt;
            int cCnt;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Excel (*.XLS;*.XLSX)|*.XLS;*.XLSX";
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                System.IO.StreamReader sr = new
                   System.IO.StreamReader(openFileDialog1.FileName);
                filename = openFileDialog1.FileName;
                sr.Close();
                f.Toggle();
            }
               // System.Data.DataTable tb = new System.Data.DataTable();
               // string filename = openFileDialog1.FileName;

            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook Book;
            Microsoft.Office.Interop.Excel.Worksheet Sheet;
            Microsoft.Office.Interop.Excel.Range Range;

            Book = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Sheet = (Microsoft.Office.Interop.Excel.Worksheet)Book.Worksheets.get_Item(1);
            Range = Sheet.UsedRange;

            for (rCnt = 1; rCnt <= Range.Rows.Count; rCnt++)
            {

                f.Tabl.Rows.Add(1);
                for (cCnt = 1; cCnt <= 13; cCnt++)
                {
                    str = (string)(Range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    f.Tabl.Rows[rCnt - 1].Cells[cCnt - 1].Value = str;
                }
            }
            Book.Close(true, null, null);
            ExcelApp.Quit();

            releaseObject(Sheet);
            releaseObject(Book);
            releaseObject(ExcelApp);
        }
Пример #18
0
        public bool CreateExcel(List<Po> pos)
        {
            xlApp = new Microsoft.Office.Interop.Excel.Application();
              xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            foreach (Po po in pos)
            {
                xlWorkSheet.Cells[po.Row, po.Column] = po.Value;
            }

             xlWorkBook.SaveAs("C:\\newcat2", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
             int test = 0;
             xlWorkBook.Close(true, misValue, misValue);
             xlApp.Quit();

             System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
             GC.Collect();
             GC.WaitForPendingFinalizers();
              return true;
        }
Пример #19
0
        private void btnExportarExcel_Click(object sender, EventArgs e)
        {
            try
            {
                saveFileDialog1.InitialDirectory = "C:";
                saveFileDialog1.Title = "Save as Excel File";
                saveFileDialog1.FileName = "";
                saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*xlsx";

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

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

                    for (int i = 0; i < dtgItensOr.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dtgItensOr.ColumnCount; j++)
                        {
                            ExcelApp.Cells[i + 2, j + 1] = dtgItensOr.Rows[i].Cells[j].Value.ToString();
                        }
                    }

                    ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #20
0
        private static void ConvertExcel(string fileName, string sourceFile)
        {
            Microsoft.Office.Interop.Excel.Workbook    excelDoc = null;
            Microsoft.Office.Interop.Excel.Application excel    = null;

            try
            {
                excel = new Microsoft.Office.Interop.Excel.Application
                {
                    Visible       = false,
                    DisplayAlerts = false
                };
                excelDoc = excel.Workbooks.Open(sourceFile, ReadOnly: true, Delimiter: ";", Format: 6, Origin: Microsoft.Office.Interop.Excel.XlPlatform.xlWindows);
                foreach (Microsoft.Office.Interop.Excel.Worksheet currentSheet in excelDoc.Worksheets)
                {
                    currentSheet.Columns.AutoFit();
                }

                excelDoc.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault);
                excel.DisplayAlerts = true;
                excelDoc.Close();
                excel.Quit();
            }
            catch (Exception ex)
            {
                try
                {
                    excel.DisplayAlerts = true;
                    excelDoc?.Close();
                    excel?.Quit();
                }
                catch { }

                throw;
            }
        }
Пример #21
0
        public static void ExportToExcel(DataGridView dg, string titulo)
        {
            // Creating a Excel object.
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = titulo;

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;



                worksheet.Columns.NumberFormat = "@";


                //Loop through each row and read value from each column.
                for (int i = -1; i < dg.Rows.Count; i++)
                {
                    for (int j = 0; j < dg.Columns.Count; j++)
                    {
                        // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                        if (cellRowIndex == 1)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dg.Columns[j].HeaderText;
                        }
                        else
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dg.Rows[i].Cells[j].Value.ToString();
                        }
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

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

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Exportación realizada");
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
Пример #22
0
        /// <summary>
        /// 打印
        /// </summary>
        private void printInfo()
        {
            Object oMissing = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Application m_objExcel = null;

            Microsoft.Office.Interop.Excel._Workbook m_objBook = null;

            Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;

            Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;

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

            try
            {

                m_objExcel = new Microsoft.Office.Interop.Excel.Application();

                DirectoryInfo Dir = new DirectoryInfo(".");

                m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Alter.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;

                m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));

                // 号牌种类
                m_objRange = m_objSheet.get_Range("D2", oMissing);
                m_objRange.Value = this.category.Text;

                // 号牌号码
                m_objRange = m_objSheet.get_Range("H2", oMissing);
                m_objRange.Value = "辽B" + this.license.Text;

                // 变更机动车所有人姓名/名称
                m_objRange = m_objSheet.get_Range("D4", oMissing);
                m_objRange.Value = this.ownerName.Text;

                //共同所有的机动车变更所有人
                m_objRange = m_objSheet.get_Range("D5", oMissing);
                m_objRange.Value = this.owner.Text;

                // 住所在车辆管理所辖区内迁移
                m_objRange = m_objSheet.get_Range("D6", oMissing);
                m_objRange.Value = this.newAddress.Text;

                // 邮寄地址
                m_objRange = m_objSheet.get_Range("D7", oMissing);
                m_objRange.Value = "          " + this.postAddress.Text;

                // 邮政编码
                m_objRange = m_objSheet.get_Range("D8", oMissing);
                m_objRange.Value = "          " + this.postcode.Text;

                // 电子信箱
                m_objRange = m_objSheet.get_Range("D9", oMissing);
                m_objRange.Value = "          " + this.email.Text;

                // 固定电话
                m_objRange = m_objSheet.get_Range("H8", oMissing);
                m_objRange.Value = "    " + this.phone.Text;

                // 移动电话
                m_objRange = m_objSheet.get_Range("H9", oMissing);
                m_objRange.Value = "    " + this.mobile.Text;

                // 省(自治县、直辖市)
                m_objRange = m_objSheet.get_Range("D10", oMissing);
                m_objRange.Value = "      " + this.province.Text;

                // 车辆管理所
                m_objRange = m_objSheet.get_Range("H10", oMissing);
                m_objRange.Value = this.department.Text;

                // 变更后的信息
                m_objRange = m_objSheet.get_Range("D15", oMissing);
                m_objRange.Value = this.information.Text;

                // 代理人姓名、名称
                m_objRange = m_objSheet.get_Range("C21", oMissing);
                m_objRange.Value = this.agentName.Text;

                // 代理人邮寄地址
                m_objRange = m_objSheet.get_Range("C22", oMissing);
                m_objRange.Value = this.agentAddress.Text;

                // 代理人邮政编码
                m_objRange = m_objSheet.get_Range("C23", oMissing);
                m_objRange.Value = this.agentPostcode.Text;

                // 代理人联系电话
                m_objRange = m_objSheet.get_Range("F23", oMissing);
                m_objRange.Value = this.agentPhone.Text;

                // 代理人电子信箱
                m_objRange = m_objSheet.get_Range("C24", oMissing);
                m_objRange.Value = this.agentEmail.Text;

                // 经办人电话
                m_objRange = m_objSheet.get_Range("C25", oMissing);
                m_objRange.Value = this.handlerName.Text;

                // 经办人联系电话
                m_objRange = m_objSheet.get_Range("F25", oMissing);
                m_objRange.Value = this.handlerPhone.Text;

                // 办理日期
                m_objRange = m_objSheet.get_Range("H20", oMissing);
                m_objRange.Value = DateTime.Today.Year + "       " + DateTime.Today.Month + "       " + DateTime.Today.Day + "   ";

                m_objRange = m_objSheet.get_Range("H25", oMissing);
                m_objRange.Value = DateTime.Today.Year + "       " + DateTime.Today.Month + "       " + DateTime.Today.Day + "   "; ;

                m_objExcel.DisplayAlerts = false;
                m_objBook.Save();
                m_objSheet.PrintOut();
            }
            catch (Exception ex)
            {
                // 输出异常信息
                MessageBox.Show("打印失败,异常信息为:" + ex.Message);
            }
            finally
            {
                if (m_objBook != null)
                {
                    m_objBook.Close(oMissing, oMissing, oMissing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                }

                if (m_objExcel != null)
                {
                    m_objExcel.Workbooks.Close();
                    m_objExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                }

                m_objBook = null;

                m_objExcel = null;

                GC.Collect();
            }
        }
Пример #23
0
        public void exportarAExcel()
        {
            try
            {
                SaveFileDialog fichero = new SaveFileDialog();
                fichero.Filter   = "Excel (*.xls)|*.xls";
                fichero.FileName = "Listado de ventas - " + DateTime.Now.ToString("dd-MM-yyyy");
                if (fichero.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.Application aplicacion;
                    Microsoft.Office.Interop.Excel.Workbook    libros_trabajo;
                    Microsoft.Office.Interop.Excel.Worksheet   hoja_trabajo;
                    aplicacion     = new Microsoft.Office.Interop.Excel.Application();
                    libros_trabajo = aplicacion.Workbooks.Add();
                    hoja_trabajo   =
                        (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);


                    if (dataLista.Rows.Count > 0)
                    {
                        //le paso el formato adecuado para los valores decimales pasando desde la fila 2 hasta datalista.rows.count+1 osea hasta el ultimo elemento
                        hoja_trabajo.Range[hoja_trabajo.Cells[2, 6], hoja_trabajo.Cells[dataLista.Rows.Count + 1, 5]].NumberFormat = "0,00";
                    }
                    //Recorremos el DataGridView rellenando la hoja de trabajo
                    for (int i = 0; i < dataLista.Columns.Count; i++)
                    {
                        hoja_trabajo.Cells[1, i + 1] = dataLista.Columns[i].Name;
                    }


                    for (int i = 0; i < dataLista.Rows.Count; i++)
                    {
                        hoja_trabajo.Cells[i + 2, 1]  = dataLista.Rows[i].Cells["codigo"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 2]  = dataLista.Rows[i].Cells["razon_social"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 3]  = dataLista.Rows[i].Cells["fecha"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 4]  = dataLista.Rows[i].Cells["tipo_comprobante"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 5]  = dataLista.Rows[i].Cells["total"].Value;
                        hoja_trabajo.Cells[i + 2, 6]  = dataLista.Rows[i].Cells["estado"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 9]  = dataLista.Rows[i].Cells["cuit"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 10] = dataLista.Rows[i].Cells["Nrocomprobante"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 11] = dataLista.Rows[i].Cells["Letra"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 12] = dataLista.Rows[i].Cells["Neto21"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 13] = dataLista.Rows[i].Cells["Totaliva21"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 14] = dataLista.Rows[i].Cells["Total_Neto105"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 15] = dataLista.Rows[i].Cells["Totaliva105"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 16] = dataLista.Rows[i].Cells["CAE"].Value.ToString();
                        hoja_trabajo.Cells[i + 2, 17] = dataLista.Rows[i].Cells["CAE_Fechavencimiento"].Value.ToString();
                    }
                    //ajustar el tamaño de las celdas deacuerdo al tamaño de las columnas agregadas
                    hoja_trabajo.Cells.Columns.AutoFit();
                    //guardo el archivo con la ruta del archivo
                    libros_trabajo.SaveAs(fichero.FileName,
                                          Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                    libros_trabajo.Close(true);
                    aplicacion.Quit();
                    if (MessageBox.Show("Desea abrir el Excel ?", "Abrir Excel"
                                        , MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
                    {
                        Process.Start(fichero.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                UtilityFrm.mensajeError("Error: " + ex.Message);
            }
        }
Пример #24
0
        static void Main(string[] args)
        {
            //add reference to Microsoft Office Interop Excell dll
            string excelFileName = "Planners.xlsx";
            string soureFilePath = AppDomain.CurrentDomain.BaseDirectory + excelFileName;

            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + excelFileName))
            {
                Microsoft.Office.Interop.Excel.Application xlApplication = new Microsoft.Office.Interop.Excel.Application();
                // xlApplication.Visible = false;
                Microsoft.Office.Interop.Excel.Workbook workbook = xlApplication.Workbooks.Open(soureFilePath);
                foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets)
                {
                    worksheet.Activate();
                    //workbook.SaveAs(AppDomain.CurrentDomain.BaseDirectory + worksheet.Name + ".txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);
                    workbook.SaveAs(AppDomain.CurrentDomain.BaseDirectory + "Planners.txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                }//End foreach worksheet
                //Close and quit workbook and xlApplication
                workbook.Close();
                xlApplication.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApplication);
                workbook      = null;
                xlApplication = null;

                //Instantiate list from DataValues.cs
                List <DataValues> dataValues = new List <DataValues>();
                //File name
                string fileName = "Planners.txt";

                //Check if 'Planners.txt' exists
                if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + fileName))
                {
                    //Generate list from reading file
                    List <string> lines = File.ReadAllLines(AppDomain.CurrentDomain.BaseDirectory + fileName).ToList();

                    //Loop through each line and set variables
                    foreach (string line in lines)
                    {
                        string[] values      = line.Split('\t'); //Tab delimited
                        char[]   charsToTrim = { '"' };          //Trim double quotes

                        //Initialize variables
                        string vendorPartner = values[1];
                        string contactFirstName;
                        string contactLastName;
                        string email;
                        string advertisingFund;
                        string baseMembership;
                        string proFixedCircular;
                        string proCustomizableCircular;
                        string proBOM;
                        string farmFixedCircular;
                        string farmCustomCircular;
                        string farmVOM;
                        string gardenMasterNationalCircular;
                        string gardenMasterVOM;
                        string proMonthlyBuys;
                        string protradeAds;
                        string proAchieversClub;
                        string totalBudget = values[26];
                        //string notes = values[27];

                        string notes = "";


                        //Logic for setting variables if data produces null or empty values
                        if (values[9] == "")
                        {
                            email = "NO_DATA";
                        }
                        else
                        {
                            email = values[9];
                        }

                        if (values[1] == "")
                        {
                            vendorPartner = "NO_DATA";
                            email         = "NO_DATA";
                        }
                        else
                        {
                            vendorPartner = values[1];
                        }

                        if (totalBudget == "0")
                        {
                            email       = "NO_DATA";
                            totalBudget = "NO_DATA";
                        }

                        if (values[7] == "")
                        {
                            contactFirstName = "NO_DATA";
                            email            = "NO_DATA";
                        }
                        else
                        {
                            contactFirstName = values[7];
                        }

                        if (values[8] == "")
                        {
                            contactLastName = "NO_DATA";
                            email           = "NO_DATA";
                        }
                        else
                        {
                            contactLastName = values[8];
                        }

                        if (values[10] == "")
                        {
                            advertisingFund = "0";
                        }
                        else
                        {
                            advertisingFund = values[10];
                        }

                        if (values[12] == "")
                        {
                            baseMembership = "0";
                        }
                        else
                        {
                            baseMembership = values[12];
                        }

                        if (values[13] == "")
                        {
                            proFixedCircular = "0";
                        }
                        else
                        {
                            proFixedCircular = values[13];
                        }

                        if (values[14] == "")
                        {
                            proCustomizableCircular = "0";
                        }
                        else
                        {
                            proCustomizableCircular = values[14];
                        }

                        if (values[15] == "")
                        {
                            proBOM = "0";
                        }
                        else
                        {
                            proBOM = values[15];
                        }

                        if (values[19] == "")
                        {
                            farmFixedCircular = "0";
                        }
                        else
                        {
                            farmFixedCircular = values[19];
                        }

                        if (values[20] == "")
                        {
                            farmCustomCircular = "0";
                        }
                        else
                        {
                            farmCustomCircular = values[20];
                        }

                        if (values[21] == "")
                        {
                            farmVOM = "0";
                        }
                        else
                        {
                            farmVOM = values[21];
                        }

                        if (values[22] == "")
                        {
                            gardenMasterNationalCircular = "0";
                        }
                        else
                        {
                            gardenMasterNationalCircular = values[22];
                        }

                        if (values[23] == "")
                        {
                            gardenMasterVOM = "0";
                        }
                        else
                        {
                            gardenMasterVOM = values[23];
                        }

                        if (values[11] == "")
                        {
                            proMonthlyBuys = "0";
                        }
                        else
                        {
                            proMonthlyBuys = values[11];
                        }

                        if (values[17] == "")
                        {
                            protradeAds = "0";
                        }
                        else
                        {
                            protradeAds = values[17];
                        }

                        if (values[16] == "")
                        {
                            proAchieversClub = "0";
                        }
                        else
                        {
                            proAchieversClub = values[16];
                        }

                        //Trim double quotes and commas from data
                        string trimmedAdvertisingFund         = advertisingFund.Trim(charsToTrim).Replace(",", "");
                        string trimmedBaseMembership          = baseMembership.Trim(charsToTrim).Replace(",", "");
                        string trimmedProFixedCircular        = proFixedCircular.Trim(charsToTrim).Replace(",", "");
                        string trimmedProCustomizableCircular = proCustomizableCircular.Trim(charsToTrim).Replace(",", "");
                        string trimmedProBOM                       = proBOM.Trim(charsToTrim).Replace(",", "");
                        string trimmedFarmFixedCircular            = farmFixedCircular.Trim(charsToTrim).Replace(",", "");
                        string trimmedFarmCustomCircular           = farmCustomCircular.Trim(charsToTrim).Replace(",", "");
                        string trimmedFarmVOM                      = farmVOM.Trim(charsToTrim).Replace(",", "");
                        string trimmedGardenMasterNationalCircular = gardenMasterNationalCircular.Trim(charsToTrim).Replace(",", "");
                        string trimmedGardenMasterVOM              = gardenMasterVOM.Trim(charsToTrim).Replace(",", "");
                        string trimmedProMonthlyBuys               = proMonthlyBuys.Trim(charsToTrim).Replace(",", "");
                        string trimmedProTradeAds                  = protradeAds.Trim(charsToTrim).Replace(",", "");
                        string trimmedProAchieversClub             = proAchieversClub.Trim(charsToTrim).Replace(",", "");
                        string trimmedNotes            = notes.Trim(charsToTrim).Replace(",", "");
                        string trimmedTotalBudget      = totalBudget.Trim(charsToTrim).Replace(",", "");
                        string trimmedVendorPartner    = vendorPartner.Trim(charsToTrim).Replace(",", "");
                        string trimmedEmail            = email.Trim(charsToTrim).Replace(",", "");
                        string trimmedContactFirstName = contactFirstName.Trim(charsToTrim).Replace(",", "");
                        string trimmedContactLastName  = contactLastName.Trim(charsToTrim).Replace(",", "");

                        //Logic to bypass header
                        if (
                            values[0] != "ContactDisplayName" &&
                            trimmedAdvertisingFund != "AdvertisingFund" &&
                            trimmedBaseMembership != "BaseMembershipProgram" &&
                            trimmedProFixedCircular != "PROHardwareFixedCircular" &&
                            trimmedProCustomizableCircular != "PROHardwareCustomizableCircular" &&
                            trimmedProBOM != "PROHardwareBargainoftheMonth" &&
                            trimmedFarmFixedCircular != "FARMMARTFixedCircular" &&
                            trimmedFarmCustomCircular != "FARMMARTCustomizableCircular" &&
                            trimmedFarmVOM != "FARMMARTValueoftheMonth" &&
                            trimmedGardenMasterNationalCircular != "GardenMasterNationalCircular" &&
                            trimmedGardenMasterVOM != "GardenMasterValueoftheMonth" &&
                            trimmedProMonthlyBuys != "PROMonthlyBuys" &&
                            trimmedProTradeAds != "PROHardwareTradeAd" &&
                            trimmedProAchieversClub != "PROHardwareAchieversClub"
                            )
                        {
                            //Convert string variables to int for addition of data values
                            //Variables for Base Membership
                            int    trimmedAdvertisingFundInt = int.Parse(trimmedAdvertisingFund);
                            int    trimmedBaseMembershipInt  = int.Parse(trimmedBaseMembership);
                            int    finalBaseMembershipInt    = trimmedAdvertisingFundInt + trimmedBaseMembershipInt;
                            string finalBaseMembership       = finalBaseMembershipInt.ToString();
                            //Variables for PRO Print Circulars
                            int trimmedProFixedCircularInt        = int.Parse(trimmedProFixedCircular);
                            int trimmedProCustomizableCircularInt = int.Parse(trimmedProCustomizableCircular);
                            int trimmedProBOMInt                       = int.Parse(trimmedProBOM);
                            int trimmedFarmFixedCircularInt            = int.Parse(trimmedFarmFixedCircular);
                            int trimmedFarmCustomCircularInt           = int.Parse(trimmedFarmCustomCircular);
                            int trimmedFarmVOMInt                      = int.Parse(trimmedFarmVOM);
                            int trimmedGardenMasterNationalCircularInt = int.Parse(trimmedGardenMasterNationalCircular);
                            int trimmedGardenMasterVOMInt              = int.Parse(trimmedGardenMasterVOM);
                            int finalPrintCircularInt                  =
                                trimmedProFixedCircularInt +
                                trimmedProCustomizableCircularInt +
                                trimmedProBOMInt +
                                trimmedFarmFixedCircularInt +
                                trimmedFarmCustomCircularInt +
                                trimmedFarmVOMInt +
                                trimmedGardenMasterNationalCircularInt +
                                trimmedGardenMasterVOMInt
                            ;
                            string finalPrintCircular = finalPrintCircularInt.ToString();

                            //Variables for Monthly Buys
                            int    trimmedProMonthlyBuysInt = int.Parse(trimmedProMonthlyBuys);
                            string finalProMonthlyBuys      = trimmedProMonthlyBuysInt.ToString();

                            //Variables for Trade Advertisements
                            int    trimmedProTradeAdsInt = int.Parse(trimmedProTradeAds);
                            string finalProtradeAds      = trimmedProTradeAdsInt.ToString();

                            //Variables for Achievers Club
                            int    trimmedProAchieversClubInt = int.Parse(trimmedProAchieversClub);
                            string finalProAchieversClub      = trimmedProAchieversClubInt.ToString();

                            //Get the total
                            int    trimmedTotalInt   = finalBaseMembershipInt + finalPrintCircularInt + trimmedProMonthlyBuysInt + trimmedProTradeAdsInt + trimmedProAchieversClubInt;
                            string finalTrimmedTotal = trimmedTotalInt.ToString();
                            if (finalTrimmedTotal == "0")
                            {
                                finalTrimmedTotal = "NO_DATA";
                            }
                            //Initialize DataValues list
                            DataValues newDataValues = new DataValues();

                            //Get data for DataValues list
                            newDataValues.Email                       = trimmedEmail;
                            newDataValues.VendorPartner               = trimmedVendorPartner;
                            newDataValues.BaseMembership              = finalBaseMembership;
                            newDataValues.PrintCircular               = finalPrintCircular;
                            newDataValues.ProMonthlyBuys              = finalProMonthlyBuys;
                            newDataValues.TradeAdvertisments          = finalProtradeAds;
                            newDataValues.AchieversClubSponsorship    = finalProAchieversClub;
                            newDataValues.TotalPromotionalBudget      = finalTrimmedTotal;
                            newDataValues.TotalPromotionalBudgetSmall = finalTrimmedTotal;
                            newDataValues.Notes                       = trimmedNotes;
                            newDataValues.ContactName                 = trimmedContactFirstName + " " + trimmedContactLastName;

                            //Set data for DataValues list
                            dataValues.Add(newDataValues);
                        }
                    }
                    //Instantiate new list for csv file
                    List <string> output = new List <string>();

                    //Instantiate new list for errors
                    List <string> errors = new List <string>();

                    //Set error file status
                    bool errorFileStatus = false;

                    //Initialize header for error file
                    errors.Add("Name,Email,VendorPartner,BaseMembership,PrintCircular,ProMonthlyBuys,TradeAdvertisments,AchieversClubSponsorship,TotalPromotionalBudget,Notes,TotalPromotionalBudgetSmall,ContactName");

                    //Initialize header for csv
                    output.Add("Sender::Name,Sender::Email,Sender::VendorPartner,Sender::BaseMembership,Sender::PrintCircular,Sender::ProMonthlyBuys,Sender::TradeAdvertisments,Sender::AchieversClubSponsorship,Sender::TotalPromotionalBudget,Sender::Notes,Sender::TotalPromotionalBudgetSmall,Sender::ContactName");

                    //Loop and add data for each line of new csv file
                    foreach (var dataValue in dataValues)
                    {   //If file has missing data, remove from CSV and create error file and set error file status to true.
                        if (dataValue.ContactName == "NO_DATA NO_DATA" || dataValue.Email == "NO_DATA" || dataValue.TotalPromotionalBudget == "NO_DATA" || dataValue.TotalPromotionalBudgetSmall == "NO_DATA")
                        {
                            output.Remove($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
                            errors.Add($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
                            errorFileStatus = true;
                        }
                        else
                        {   //If file has no errors, create CSV
                            output.Add($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
                        }
                    }

                    //Create unique file name using current date time down to the minute
                    DateTime currentTime           = DateTime.Now;
                    string   now                   = currentTime.ToString("MM_dd_yyyy_HHmmss");
                    string   docusignFileName      = "Docusign_Upload";
                    string   dotCsv                = ".csv";
                    string   docusignErrorFileName = "Docusign_Errors";

                    //If error file status is true, write error file and write cleaned CSV file.
                    if (errorFileStatus == true)
                    {
                        File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignErrorFileName + "_" + now + dotCsv, errors);
                        File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignFileName + "_" + now + dotCsv, output);
                        Console.WriteLine("Docusign CSV File created successfully! ");
                        Console.WriteLine();
                        Console.WriteLine("Your formatted Docusign CSV file is in this location:");
                        Console.WriteLine(Directory.GetCurrentDirectory());
                        Console.WriteLine();
                        Console.WriteLine("***Errors detected*** \r\n See Docusign_Error file for details.");
                        Console.WriteLine();
                        //File.Delete(fileName);
                        Console.WriteLine("You are now being directed to Docusign.com");
                        File.Delete(fileName);
                        System.Diagnostics.Process.Start("https://account.docusign.com/");
                        Thread.Sleep(3000);
                        System.Environment.Exit(0);
                        //Console.ReadLine();
                    }
                    else
                    {   //If no errors, write CSV file.
                        File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignFileName + "_" + now + dotCsv, output);
                        Console.WriteLine("Docusign CSV File created successfully! ");
                        Console.WriteLine();
                        Console.WriteLine("Your formatted Docusign CSV file is in this location:");
                        Console.WriteLine(Directory.GetCurrentDirectory());
                        Console.WriteLine();
                        //File.Delete(fileName);
                        Console.WriteLine("You are now being directed to Docusign.com");
                        File.Delete(fileName);
                        System.Diagnostics.Process.Start("https://account.docusign.com/");
                        Thread.Sleep(3000);
                        System.Environment.Exit(0);
                        //Console.ReadLine();
                    }
                }//End if for fileName
                else
                {
                    //If Planners.txt does not exist, raise error.
                    Console.WriteLine("Excel file 'Planners.txt' does not exist in the current directory!");
                    Console.ReadLine();
                }
            }
            else
            {   //If Planners.txt does not exist, raise error.
                Console.WriteLine("Excel file 'Planners.xlsx' does not exist in the current directory!");
                Console.ReadLine();
            }

            //End old docusignplanners code/////////////////////////////////
        } //End Main()
Пример #25
0
        private static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
        {
            //System.Data.DataTable dt = new System.Data.DataTable();

            if (dt == null)
            {
                return;
            }

            Microsoft.Office.Interop.Excel._Workbook oWB;
            Microsoft.Office.Interop.Excel.Series    oSeries;
            //Microsoft.Office.Interop.Excel.Range oResizeRange;
            Microsoft.Office.Interop.Excel._Chart oChart;
            //String sMsg;
            //int iNumQtrs;
            GC.Collect();//系统的垃圾回收

            //string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
            //Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            //Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);

            Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook   wb = ep.Workbooks.Add(true);


            if (ep == null)
            {
                MsgBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }


            ep.Visible = true;
            Microsoft.Office.Interop.Excel.Sheets     sheets = wb.Worksheets;
            Microsoft.Office.Interop.Excel._Worksheet ws     = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
            ws.UsedRange.Select();
            ws.UsedRange.Copy(System.Type.Missing);
            // wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
            int rowIndex = 1;
            int colIndex = 1;

            foreach (DataColumn col in dt.Columns)
            {
                ws.Cells[rowIndex, colIndex++] = col.ColumnName;
            }

            for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
            {
                DataRow row = dt.Rows[drvIndex];
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
                    colIndex++;
                }
            }


            oWB    = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
            oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
                                                                           Missing.Value, Missing.Value);

            int rcount = dt.Rows.Count;
            int ccount = dt.Columns.Count;

            oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
                               Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
                               charttitle, Missing.Value, Missing.Value, Missing.Value);
            // oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);

            //string str = String.Empty;
            //for (int I = 1; I < 15; I++)

            //{
            //    str += I.ToString() + "\t";
            //}
            //try { oSeries.XValues = str; }
            //catch { }
            //  oSeries.HasDataLabels = true;
            //   string charttitle ,bool tableflag ,bool bl)
            if (tableflag == true)
            {
                oChart.HasDataTable = true;
            }
            else
            {
                oChart.HasDataTable = false;
            }
            oChart.PlotVisibleOnly = false;
            //



            Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
                Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
                Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            //axis.HasTitle = true;
            //axis.AxisTitle.Text = "Sales Figures";
            // axis.HasMinorGridlines=true;
            Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
                Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            //ax.HasTitle = true;
            //ax.AxisTitle.Text = "Sales Figures";
            ax.HasMajorGridlines = true;


            //  string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
            //  ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            try
            {
                wb.Saved = true;
                wb.SaveCopyAs(filename);
            }
            catch (Exception ex)
            {
                MsgBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
            }
            ep.Quit();

            GC.Collect();//强行销毁
        }
Пример #26
0
        //http://csharp.net-informations.com/excel/csharp-read-excel.htm
        //http://csharp.net-informations.com/excel/csharp-create-excel.htm
        // Just take the predicted class + its probability and save it in a new file
        private void btnSingleFile_Click(object sender, EventArgs e)
        {
            // int[] rowsToBeProcessed = new int[] { 3,29, 34, 47, 59, 71, 98, 109, 128, 129, 143, 158, 174, 195, 212, 239, 245, 251, 260, 271, 284, 294, 302, 333, 341, 347, 399, 402, 444,473, 478, 496, 502, 526, 535, 544, 555, 571, 581, 585, 601, 627, 642,660, 676, 681, 700, 763,791, 811,945 };
            int noOfSheets = 101;// 31

            int[] rowsToBeProcessed = new int[] { };

            // process user input entered rows
            if (txtInstanceNo.Text != string.Empty)
            {
                rowsToBeProcessed = Array.ConvertAll(txtInstanceNo.Text.Split(','), s => int.Parse(s));
            }
            else
            {
                // process explicitly added row numbers
                rowsToBeProcessed = new int[]  { 682 };
            }


            foreach (int row in rowsToBeProcessed)
            {
                string writeFileLocation = txtSingleFileWriteLocation.Text + "\\" + row + ".xls";
                // Reading Excel Sheet
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook  xlReadWorkBook  = xlApp.Workbooks.Open(txtSingleFileReadLocation.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel.Worksheet xlReadWorkSheet = null;

                //Writing Excel Sheet
                Microsoft.Office.Interop.Excel.Workbook  xlWriteWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWriteWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlWriteWorkBook  = xlApp.Workbooks.Add(misValue);
                xlWriteWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWriteWorkBook.Worksheets.get_Item(1);

                xlWriteWorkSheet.Cells[1, 1]  = "Class0";
                xlWriteWorkSheet.Cells[1, 2]  = "Class1";
                xlWriteWorkSheet.Cells[1, 3]  = "Class2";
                xlWriteWorkSheet.Cells[1, 4]  = "Class3";
                xlWriteWorkSheet.Cells[1, 5]  = "Class4";
                xlWriteWorkSheet.Cells[1, 6]  = "Class5";
                xlWriteWorkSheet.Cells[1, 7]  = "Class6";
                xlWriteWorkSheet.Cells[1, 8]  = "Class7";
                xlWriteWorkSheet.Cells[1, 9]  = "Class8";
                xlWriteWorkSheet.Cells[1, 10] = "Class9";
                int writeRowCount = 2;

                string predictedClass;
                string probabilityOfPredictedClass;
                int    rw = 0;
                int    cl = 0;
                Microsoft.Office.Interop.Excel.Range range;

                int rowToBeProcessed = row;// = Convert.ToInt16(txtInstanceNo.Text);

                // count the nubmer of times specific class was predicted
                int class0Count = 0;
                int class1Count = 0;
                int class2Count = 0;
                int class3Count = 0;
                int class4Count = 0;
                int class5Count = 0;
                int class6Count = 0;
                int class7Count = 0;
                int class8Count = 0;
                int class9Count = 0;

                //for (int i = 2; i <= 101; i++)
                for (int i = 2; i <= noOfSheets; i++)
                {
                    xlReadWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBook.Worksheets.get_Item(i);
                    range           = xlReadWorkSheet.UsedRange;
                    rw             = range.Rows.Count;
                    cl             = range.Columns.Count;
                    predictedClass = (string)(range.Cells[rowToBeProcessed, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
                    probabilityOfPredictedClass = (string)(range.Cells[rowToBeProcessed, 5] as Microsoft.Office.Interop.Excel.Range).Value2;

                    //Write in Excel Sheet, this library has start index from 1 (so we have to add 1)
                    xlWriteWorkSheet.Cells[writeRowCount, Convert.ToInt16(predictedClass) + 1] = probabilityOfPredictedClass;
                    writeRowCount = writeRowCount + 1;

                    #region

                    if (predictedClass == "0")
                    {
                        class0Count = class0Count + 1;
                    }
                    else if (predictedClass == "1")
                    {
                        class1Count = class1Count + 1;
                    }
                    else if (predictedClass == "2")
                    {
                        class2Count = class2Count + 1;
                    }
                    else if (predictedClass == "3")
                    {
                        class3Count = class3Count + 1;
                    }
                    else if (predictedClass == "4")
                    {
                        class4Count = class4Count + 1;
                    }
                    else if (predictedClass == "5")
                    {
                        class5Count = class5Count + 1;
                    }
                    else if (predictedClass == "6")
                    {
                        class6Count = class6Count + 1;
                    }
                    else if (predictedClass == "7")
                    {
                        class7Count = class7Count + 1;
                    }
                    else if (predictedClass == "8")
                    {
                        class8Count = class8Count + 1;
                    }
                    else
                    {
                        class9Count = class9Count + 1;
                    }

                    #endregion
                }

                // Write the count (number of times the corresponsing class has been predicted) for each class

                xlWriteWorkSheet.Cells[103, 1]  = class0Count;
                xlWriteWorkSheet.Cells[103, 2]  = class1Count;
                xlWriteWorkSheet.Cells[103, 3]  = class2Count;
                xlWriteWorkSheet.Cells[103, 4]  = class3Count;
                xlWriteWorkSheet.Cells[103, 5]  = class4Count;
                xlWriteWorkSheet.Cells[103, 6]  = class5Count;
                xlWriteWorkSheet.Cells[103, 7]  = class6Count;
                xlWriteWorkSheet.Cells[103, 8]  = class7Count;
                xlWriteWorkSheet.Cells[103, 9]  = class8Count;
                xlWriteWorkSheet.Cells[103, 10] = class9Count;

                //xlWriteWorkBook.SaveAs(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\ExcelProcesser\ProcessedResults\MR52\MRDecision\Class9\" + row + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
                //xlWriteWorkBook.SaveAs(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\ExcelProcesser\ProcessedResults_Clem\Origional\Origional_20.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
                xlWriteWorkBook.SaveAs(writeFileLocation, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
                xlWriteWorkBook.Close(true, misValue, misValue);
                xlReadWorkBook.Close(true, null, null);
                xlApp.Quit();

                Marshal.ReleaseComObject(xlReadWorkSheet);
                Marshal.ReleaseComObject(xlReadWorkBook);
                Marshal.ReleaseComObject(xlWriteWorkSheet);
                Marshal.ReleaseComObject(xlWriteWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }
            MessageBox.Show("Process Completed Successfully.");
        }
        //打印功能
        public int print(bool isShow)
        {
            // 打开一个Excel进程
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            // 利用这个进程打开一个Excel文件
            Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\18 QB-PA-PP-03-R02A 产品热合强度检验记录.xlsx");
            // 选择一个Sheet,注意Sheet的序号是从1开始的
            Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
            // 修改Sheet中某行某列的值
            my = printValue(my, wb);

            if (isShow)
            {
                //true->预览
                // 设置该进程是否可见
                oXL.Visible = true;
                // 让这个Sheet为被选中状态
                my.Select();  // oXL.Visible=true 加上这一行  就相当于预览功能
                return(0);
            }
            else
            {
                int  pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
                bool isPrint   = true;
                //false->打印
                try
                {
                    // 设置该进程是否可见
                    //oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
                    // 直接用默认打印机打印该Sheet
                    my.PrintOut();
                }
                catch
                { isPrint = false; }
                finally
                {
                    if (isPrint)
                    {
                        //写日志
                        string log = "=====================================\n";
                        //log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 打印文档\n";
                        log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒 打印文档\n");
                        dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;

                        bsOuter.EndEdit();
                        daOuter.Update((DataTable)bsOuter.DataSource);
                    }
                    // 关闭文件,false表示不保存
                    wb.Close(false);
                    // 关闭Excel进程
                    oXL.Quit();
                    // 释放COM资源
                    Marshal.ReleaseComObject(wb);
                    Marshal.ReleaseComObject(oXL);
                    wb  = null;
                    oXL = null;
                    my  = null;
                }
                return(pageCount);
            }
        }
Пример #28
0
        public int print(bool b)
        {
            int label_打印成功 = 1;

            // 打开一个Excel进程
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            // 利用这个进程打开一个Excel文件
            string dir = System.IO.Directory.GetCurrentDirectory();

            dir += "./../../xls/CSBag/SOP-MFG-110-R01A 清场记录.xlsx";
            Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
            // 选择一个Sheet,注意Sheet的序号是从1开始的
            Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
            // 修改Sheet中某行某列的值
            fill_excel(my);

            //"生产指令-步骤序号- 表序号 /&P"
            int            sheetnum;
            SqlDataAdapter da = new SqlDataAdapter("select ID from 清场记录" + " where 生产指令ID=" + ID.ToString(), mySystem.Parameter.conn);
            DataTable      dt = new DataTable("temp");

            da.Fill(dt);
            List <Int32> sheetList = new List <Int32>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
            }
            sheetnum = sheetList.IndexOf(Convert.ToInt32(dtOuter.Rows[0]["ID"])) + 1;
            my.PageSetup.RightFooter = CODE + "-" + sheetnum.ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;  // &P 是页码

            if (b)
            {
                // 设置该进程是否可见
                oXL.Visible = true;
                // 让这个Sheet为被选中状态
                my.Select();  // oXL.Visible=true 加上这一行  就相当于预览功能
                return(0);
            }
            else
            {
                // 直接用默认打印机打印该Sheet
                try
                {
                    my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
                }
                catch
                {
                    label_打印成功 = 0;
                }

                if (1 == label_打印成功)
                {
                    string str角色;
                    if (_userState == 0)
                    {
                        str角色 = "操作员";
                    }
                    else if (_userState == 1)
                    {
                        str角色 = "审核员";
                    }
                    else
                    {
                        str角色 = "管理员";
                    }
                    string log = "\n=====================================\n";
                    log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + str角色 + ":" + mySystem.Parameter.userName + " 完成打印\n";
                    dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
                    bsOuter.EndEdit();
                    daOuter.Update((DataTable)bsOuter.DataSource);
                }
                int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
                // 关闭文件,false表示不保存
                wb.Close(false);
                // 关闭Excel进程
                oXL.Quit();
                // 释放COM资源
                Marshal.ReleaseComObject(wb);
                Marshal.ReleaseComObject(oXL);
                wb  = null;
                oXL = null;
                return(pageCount);
            }
        }
Пример #29
0
        /// 导出为Excel格式文件
        /// </summary>
        /// <param name="counttable">统计数据</param>
        /// <param name="dt">作为数据源的DataTable</param>
        /// <param name="saveFile">带路径的保存文件名</param>
        /// <param name="title">一个Excel sheet的标题</param>
        void exportExcel(DataTable counttable, DataTable dt, string saveFile, string title, string senderdoctor, string recieverdoctor, DateTime handovertime)
        {
            Microsoft.Office.Interop.Excel.Application rptExcel = new Microsoft.Office.Interop.Excel.Application();
            if (rptExcel == null)
            {
                MessageBox.Show("无法打开EXcel,请检查Excel是否可用或者是否安装好Excel", "系统提示");
                return;
            }
            int   rowCount    = dt.Rows.Count;    //行数
            int   columnCount = dt.Columns.Count; //列数
            float percent     = 0;                //导出进度

            //this.Cursor = Cursors.WaitCursor;
            //保存文化环境
            System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Microsoft.Office.Interop.Excel.Workbook  workbook  = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
            worksheet.Name = "报表";//一个sheet的名称

            //rptExcel.Visible = true;//打开导出的Excel文件

            //填充统计数据
            int countrows = 1;
            int countcols = counttable.Rows.Count * 2;

            object[,] objcountdata = new object[countrows, countcols];
            for (int i = 0; i < countcols; i++)
            {
                int j = i / 2;
                int k = i % 2;
                objcountdata[0, i] = counttable.Rows[j][k];
            }

            Microsoft.Office.Interop.Excel.Range countrange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]);
            countrange.Value2 = objcountdata;

            worksheet.Cells[3, 1] = title;//表标题

            //填充列标题
            for (int i = 0; i < columnCount; i++)
            {
                worksheet.Cells[4, i + 1] = dt.Columns[i].ColumnName;
            }

            //创建对象数组存储DataTable的数据,这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            object[,] objData = new object[rowCount, columnCount];

            //填充内容到对象数组
            for (int r = 0; r < rowCount; r++)
            {
                for (int col = 0; col < columnCount; col++)
                {
                    objData[r, col] = dt.Rows[r][col].ToString();
                }
                percent = ((float)(r + 1) * 100) / rowCount;
                //this.panelProgress.Visible = true;//显示进度条
                //this.lblPercents.Text=percent.ToString("n") + "%";
                //this.progressBar1.Value = Convert.ToInt32(percent);
                System.Windows.Forms.Application.DoEvents();
            }
            //将对象数组的值赋给Excel对象
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowCount + 4, columnCount]);
            //range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.NumberFormat = "@";//设置数字文本格式
            range.Value2       = objData;

            string handinfo = senderdoctor + "     " + recieverdoctor + "     " + handovertime.ToString("yyyy-MM-dd HH:mm");

            worksheet.Cells[5 + rowCount, 1] = handinfo;

            //设置格式

            //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).RowHeight = 38;
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).Font.Bold = true;
            //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Name = "黑体";
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).MergeCells = true;//合并单元格
            worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).MergeCells = true;
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
            worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).RowHeight = 38;
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).Font.Bold = true;
            worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).RowHeight = 38;
            worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).Font.Bold = true;
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).Font.Size       = 16;
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).Borders.LineStyle = 1;              //设置边框
            worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 5, columnCount]).Borders.LineStyle = 1; //设置边框
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 5, columnCount]).Columns.AutoFit();     //设置单元格宽度为自适应

            //恢复文化环境
            System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
            try
            {
                //rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里,如果不用SaveFileDialog就可用这种方法
                workbook.Saved = true;
                workbook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
                //this.Cursor = Cursors.Default;
                //this.panelProgress.Visible = false;//隐藏进度条
                MessageBox.Show("导出成功!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出文件出错,文件可能正被打开,具体原因:" + ex.Message, "出错信息");
            }
            finally
            {
                dt.Dispose();
                rptExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rptExcel);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                GC.Collect();
                KillAllExcel();
            }
        }
Пример #30
0
        public bool ExportarTodo(DataGridView grd)
        {
            try
            {
                bool           okFile  = false;
                SaveFileDialog fichero = new SaveFileDialog();
                fichero.Filter = "Excel (*.xls)|*.xls";
                if (fichero.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.Application aplicacion;
                    Microsoft.Office.Interop.Excel.Workbook    libros_trabajo;
                    Microsoft.Office.Interop.Excel.Worksheet   hoja_trabajo;

                    aplicacion     = new Microsoft.Office.Interop.Excel.Application();
                    libros_trabajo = aplicacion.Workbooks.Add();
                    hoja_trabajo   =
                        (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);

                    int ColumnIndex = 0;
                    foreach (DataGridViewColumn col in grd.Columns)
                    {
                        ColumnIndex++;
                        hoja_trabajo.Cells[1, ColumnIndex] = col.HeaderText;
                    }

                    string LetraColumna = string.Empty;
                    switch (ColumnIndex)
                    {
                    case 1:
                        LetraColumna = "A";
                        break;

                    case 2:
                        LetraColumna = "B";
                        break;

                    case 3:
                        LetraColumna = "C";
                        break;

                    case 4:
                        LetraColumna = "D";
                        break;

                    case 5:
                        LetraColumna = "E";
                        break;

                    case 6:
                        LetraColumna = "F";
                        break;

                    case 7:
                        LetraColumna = "G";
                        break;

                    case 8:
                        LetraColumna = "H";
                        break;

                    case 9:
                        LetraColumna = "I";
                        break;

                    case 10:
                        LetraColumna = "J";
                        break;

                    case 11:
                        LetraColumna = "K";
                        break;

                    case 12:
                        LetraColumna = "L";
                        break;

                    case 13:
                        LetraColumna = "M";
                        break;

                    case 14:
                        LetraColumna = "N";
                        break;

                    case 15:
                        LetraColumna = "O";
                        break;

                    case 16:
                        LetraColumna = "Q";
                        break;

                    case 17:
                        LetraColumna = "P";
                        break;

                    case 18:
                        LetraColumna = "R";
                        break;

                    case 19:
                        LetraColumna = "S";
                        break;

                    case 20:
                        LetraColumna = "T";
                        break;

                    case 21:
                        LetraColumna = "U";
                        break;

                    case 22:
                        LetraColumna = "V";
                        break;

                    case 23:
                        LetraColumna = "W";
                        break;
                    }


                    Microsoft.Office.Interop.Excel.Range rango = hoja_trabajo.get_Range("A1", LetraColumna + "1");
                    rango.Font.Bold = true;
                    rango.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                    rango.Interior.ColorIndex       = 15;

                    int rowIndex = 1;

                    foreach (DataGridViewRow row in grd.Rows)
                    {
                        int Column = 0;
                        rowIndex++;
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            if (grd.Columns[Column].DefaultCellStyle.Format == "C" || grd.Columns[Column].DefaultCellStyle.Format == "C2")
                            {
                                hoja_trabajo.Cells[rowIndex, Column + 1].NumberFormat = "$#,##0.00";
                            }
                            else if (grd.Columns[Column].DefaultCellStyle.Format == "P0" || grd.Columns[Column].DefaultCellStyle.Format == "P2")
                            {
                                hoja_trabajo.Cells[rowIndex, j + 1].NumberFormat = "0.00%";
                            }
                            else
                            {
                                hoja_trabajo.Cells[rowIndex, Column + 1].NumberFormat = "@";
                            }
                            if (row.Cells[j].Style.BackColor.Name == "0")
                            {
                                hoja_trabajo.Cells[rowIndex, Column + 1].Interior.Color = Color.White;
                            }
                            else
                            {
                                hoja_trabajo.Cells[rowIndex, Column + 1].Interior.Color = row.Cells[j].Style.BackColor;//ColorTranslator.ToOle(row.Cells[rowIndex - 1].Style.BackColor);
                            }
                            if (row.Cells[j].Style.BackColor == Color.Red)
                            {
                                hoja_trabajo.Cells[rowIndex, Column + 1].Font.Color = Color.White;
                            }
                            hoja_trabajo.Cells[rowIndex, Column + 1] = row.Cells[j].Value;

                            Column++;
                        }
                    }


                    rango.RowHeight = 20;
                    hoja_trabajo.Columns.AutoFit();
                    libros_trabajo.SaveAs(fichero.FileName,
                                          Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                    libros_trabajo.Close(true);
                    aplicacion.Quit();
                    okFile = true;
                    return(okFile);
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ocurrió un error inesperado: " + ex.Message, "HalcoNET", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }
        }
Пример #31
0
 public bool TableToExcel(string strfilename)
 {
     try
     {
         Microsoft.Office.Interop.Excel.Application app      = new Microsoft.Office.Interop.Excel.Application();
         Microsoft.Office.Interop.Excel.Workbook    book     = app.Workbooks.Open(strfilename, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
         Microsoft.Office.Interop.Excel.Worksheet   wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets["DeSo"];
         int h = 1;
         wrksheet.Cells[2, 10] = "* Thời gian:" + timeFisrt.Text + "/" + dtp_FirstDay.Value.Day + "/" + dtp_FirstDay.Value.Month + "/" + dtp_FirstDay.Value.Year + " đến " + timeEnd.Text + "/" + dtp_EndDay.Value.Day + "/" + dtp_EndDay.Value.Month + "/" + dtp_EndDay.Value.Year;
         for (int i = 0; i < dataGridView1.RowCount; i++)
         {
             wrksheet.Cells[h + 2, 1] = h;
             wrksheet.Cells[h + 2, 2] = dataGridView1.Rows[i].Cells[0].Value + ""; //username
             wrksheet.Cells[h + 2, 3] = dataGridView1.Rows[i].Cells[1].Value + ""; //fullname
             wrksheet.Cells[h + 2, 4] = dataGridView1.Rows[i].Cells[2].Value + ""; //tong
             wrksheet.Cells[h + 2, 5] = dataGridView1.Rows[i].Cells[3].Value + ""; //phieudung
             wrksheet.Cells[h + 2, 6] = dataGridView1.Rows[i].Cells[4].Value + ""; //phieusai
             wrksheet.Cells[h + 2, 7] = dataGridView1.Rows[i].Cells[5].Value + ""; //thoigian
             wrksheet.Cells[h + 2, 8] = dataGridView1.Rows[i].Cells[6].Value + ""; //hieusuat
             h++;
         }
         Microsoft.Office.Interop.Excel.Worksheet wrksheetjp = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets["DeJP"];
         int jp = 1;
         wrksheetjp.Cells[2, 10] = "* Thời gian:" + timeFisrt.Text + "/" + dtp_FirstDay.Value.Day + "/" + dtp_FirstDay.Value.Month + "/" + dtp_FirstDay.Value.Year + " đến " + timeEnd.Text + "/" + dtp_EndDay.Value.Day + "/" + dtp_EndDay.Value.Month + "/" + dtp_EndDay.Value.Year;
         for (int i = 0; i < dataGridView2.RowCount; i++)
         {
             wrksheetjp.Cells[jp + 2, 1] = jp;
             wrksheetjp.Cells[jp + 2, 2] = dataGridView2.Rows[i].Cells[0].Value + ""; //username
             wrksheetjp.Cells[jp + 2, 3] = dataGridView2.Rows[i].Cells[1].Value + ""; //fullname
             wrksheetjp.Cells[jp + 2, 4] = dataGridView2.Rows[i].Cells[2].Value + ""; //tong
             wrksheetjp.Cells[jp + 2, 5] = dataGridView2.Rows[i].Cells[3].Value + ""; //phieudung
             wrksheetjp.Cells[jp + 2, 6] = dataGridView2.Rows[i].Cells[4].Value + ""; //phieusai
             wrksheetjp.Cells[jp + 2, 7] = dataGridView2.Rows[i].Cells[5].Value + ""; //thoigian
             wrksheetjp.Cells[jp + 2, 8] = dataGridView2.Rows[i].Cells[6].Value + ""; //hieusuat
             jp++;
         }
         //copyAlltoClipboard();
         //Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)wrksheet.Cells[3, 1];
         //CR.Select();
         //wrksheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
         string         savePath;
         SaveFileDialog saveFileDialog1 = new SaveFileDialog();
         saveFileDialog1.Title            = @"Save Excel Files";
         saveFileDialog1.Filter           = @"Excel files (*.xlsx)|*.xlsx";
         saveFileDialog1.FileName         = "NangSuat_BaoCaoLuong2018_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
         saveFileDialog1.RestoreDirectory = true;
         if (saveFileDialog1.ShowDialog() == DialogResult.OK)
         {
             book.SaveCopyAs(saveFileDialog1.FileName);
             book.Saved = true;
             savePath   = Path.GetDirectoryName(saveFileDialog1.FileName);
             app.Quit();
         }
         else
         {
             MessageBox.Show(@"Error exporting excel!");
             return(false);
         }
         if (savePath != null)
         {
             Process.Start(savePath);
         }
         return(true);
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
         return(false);
     }
 }
Пример #32
0
        public static string multiSheetExcel(DataSet ds)
        {
            DataTable table100      = ds.Tables[0];
            DataTable table50first  = ds.Tables[1];
            DataTable table50second = ds.Tables[2];
            DataTable table50full   = ds.Tables[3];
            DataTable table5050     = ds.Tables[4];
            DataTable tableCn       = ds.Tables[5];
            DataTable tableRmb      = ds.Tables[6];

            string filename = "";

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook    workbook = excelApp.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel.Sheets      sheets   = workbook.Worksheets;

            for (int i = sheets.Count; i > 1; i--)
            {
                ((Microsoft.Office.Interop.Excel.Worksheet)sheets[i]).Delete();
            }

            for (int i = 1; i <= 6; i++)
            {
                workbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
            }

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

            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();
                }
            }

            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";

            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();
                }
            }

            Microsoft.Office.Interop.Excel.Worksheet sheet5 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[5];
            sheet5.Name = "50% MPA 100% 1+1 pcs";

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

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

            Microsoft.Office.Interop.Excel.Worksheet sheet6 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[6];
            sheet6.Name = "OEM=CN";

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

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

            Microsoft.Office.Interop.Excel.Worksheet sheet7 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[7];
            sheet7.Name = "RMB";

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

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

            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);
        }
Пример #33
0
        private void ExportExcels(string fileName, DataGridView myDGV)
        {
            string         saveFileName = "";
            SaveFileDialog saveDialog   = new SaveFileDialog();

            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter     = "Excel文件|*.xlsx";
            saveDialog.FileName   = fileName;
            //saveDialog.ShowDialog();
            if (saveDialog.ShowDialog() == DialogResult.OK)
            {
                saveFileName = saveDialog.FileName;
                if (saveFileName.IndexOf(":") < 0)
                {
                    return;                                //点了取消
                }
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建Excel对象,可能您的PC未安装Excel");
                    return;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook  workbook  = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
                                                                                                                                       //写入标题
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
                }
                //写入数值
                for (int r = 0; r < myDGV.Rows.Count; r++)
                {
                    for (int i = 0; i < myDGV.ColumnCount; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
                if (saveFileName != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(saveFileName);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                    }
                }
                xlApp.Quit();
                GC.Collect();//强行销毁
                MessageBox.Show("数据已保存到文件: " + fileName, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("已取消保存文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public string ExportExcel(string SQL, string[] StrCloumns, string saveFileName)
        {
            try
            {
                DataTable      Table0 = new DataTable();
                SqlDataAdapter sda    = new SqlDataAdapter(SQL, TempConn.GetConnStr());
                sda.Fill(Table0);
                DataSet ds = new DataSet();
                ds.Tables.Add(Table0);
                ChangeColumnName(ref ds, StrCloumns);
                if (ds == null)
                {
                    return("数据库为空");
                }

                bool fileSaved = false;
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    return("无法创建Excel对象,可能您的机子未安装Excel");
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook  workbook  = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
                                                                                                                                       //写入字段
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                }
                //写入数值
                for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
                {
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
                if (saveFileName != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(saveFileName);
                        fileSaved = true;
                    }
                    catch (Exception ex)
                    {
                        fileSaved = false;
                        MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                        return("");
                    }
                }
                else
                {
                    fileSaved = false;
                }
                xlApp.Quit();
                GC.Collect();//强行销毁
                if (fileSaved && System.IO.File.Exists(saveFileName))
                {
                    System.Diagnostics.Process.Start(saveFileName);                                                   //打开EXCEL
                }
                MessageBox.Show("导出成功,默认保存在:我的电脑/文档");
                return("成功保存到Excel");
            }
            catch (Exception ex)
            {
                return(ex.ToString());
            }
        }
Пример #35
0
        private void SaveIntervalExcel_Click(object sender, RoutedEventArgs e)
        {
            if (intervalControl.Open)
            {
                System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
                sfd.Filter = "Документ Excel(*.xlsx)|*.xlsx";
                if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
                {
                    return;
                }
                string   filename             = sfd.FileName;
                int      rows                 = intervalControl.IntervalGrid.Count();
                double[] lBorder              = intervalControl.IntervalGrid.Select(p => p.leftBorder).ToArray();
                double[] rBorder              = intervalControl.IntervalGrid.Select(p => p.rightBorder).ToArray();
                double[] frequency            = intervalControl.IntervalGrid.Select(p => p.frequency).ToArray();
                double[] accumulatedFrequency = intervalControl.IntervalGrid.Select(p => p.accumulatedFrequency).ToArray();
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook    ExcelWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet   ExcelWorkSheet;
                ExcelWorkBook                        = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
                ExcelWorkSheet                       = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
                ExcelWorkSheet.Cells[1, 1]           = "Левая граница";
                ExcelWorkSheet.Cells[1, 2]           = "Правая граница";
                ExcelWorkSheet.Cells[1, 3]           = "Частота";
                ExcelWorkSheet.Cells[1, 4]           = "Накопленная частота";
                ExcelWorkSheet.Cells[1, 5]           = "Расчеты";
                ExcelWorkSheet.Cells[1, 1].Font.Bold = true;
                ExcelWorkSheet.Cells[1, 2].Font.Bold = true;
                ExcelWorkSheet.Cells[1, 3].Font.Bold = true;
                ExcelWorkSheet.Cells[1, 4].Font.Bold = true;
                ExcelWorkSheet.Cells[1, 5].Font.Bold = true;
                for (int i = 0; i < rows; i++)
                {
                    ExcelWorkSheet.Cells[i + 2, 1] = lBorder[i];
                    ExcelWorkSheet.Cells[i + 2, 2] = rBorder[i];
                    ExcelWorkSheet.Cells[i + 2, 3] = frequency[i];
                    ExcelWorkSheet.Cells[i + 2, 4] = accumulatedFrequency[i];
                }

                ExcelWorkSheet.Cells[2, 5]  = intervalControl.tbAverageValue.Text;
                ExcelWorkSheet.Cells[3, 5]  = intervalControl.tbMode.Text;
                ExcelWorkSheet.Cells[4, 5]  = intervalControl.tbMedian.Text;
                ExcelWorkSheet.Cells[5, 5]  = intervalControl.tbRangeOfVariation.Text;
                ExcelWorkSheet.Cells[6, 5]  = intervalControl.tbMeanLinearDeviation.Text;
                ExcelWorkSheet.Cells[7, 5]  = intervalControl.tbDispersion.Text;
                ExcelWorkSheet.Cells[8, 5]  = intervalControl.tbStandardDeviation.Text;
                ExcelWorkSheet.Cells[9, 5]  = intervalControl.tbCoefficientVariation.Text;
                ExcelWorkSheet.Cells[10, 5] = intervalControl.tbNormalCoefficientAsymmetry.Text;
                ExcelWorkSheet.Cells[11, 5] = intervalControl.tbEstimationCoefficientAsymmetry.Text;
                ExcelWorkSheet.Cells[12, 5] = intervalControl.tbDegreeAsymmetry.Text;
                ExcelWorkSheet.Cells[13, 5] = intervalControl.tbMaterialityAsymmetry.Text;
                ExcelWorkSheet.Cells[14, 5] = intervalControl.tbExcess.Text;
                ExcelWorkSheet.Cells[15, 5] = intervalControl.tbExcessError.Text;

                ExcelWorkSheet.Columns.AutoFit();

                ExcelWorkBook.SaveAs(filename);
                ExcelWorkBook.Close();
                ExcelApp.Quit();
            }
            else
            {
                dialogError.IsOpen = true;
            }
        }
Пример #36
0
        private void repIn_Click(object sender, EventArgs e)
        {
            DataRow row = layoutView1.GetDataRow(layoutView1.FocusedRowHandle);
            PrintDocument thePrint = new PrintDocument();
            PrintDialog print = new PrintDialog();
            print.Document = thePrint;
            thePrint.PrintPage += new PrintPageEventHandler(thePrint_PrintPage);
            Path_new = FrameworkParams.TEMP_FOLDER + @"\" + row["TEN_FILE"].ToString();

            byte[] a = row["NOI_DUNG"] as byte[];
            if (a == null || a.Length == 0) return;
            HelpByte.BytesToFile(a, Path_new);
            try
            {
                pic = Image.FromFile(Path_new);
            }
            catch
            { pic = null; }
            read = new System.IO.StreamReader(Path_new, System.Text.Encoding.Default, true);
            #region print word file
            if (Path.GetExtension(Path_new).ToLower().Equals(".doc")
                || Path.GetExtension(Path_new).ToLower().Equals(".docx"))
            {
                Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
                object fileName = Path_new;
                object nullobj = Missing.Value;
                wordApp.Visible = true;
                Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open(
                    ref fileName, ref nullobj, ref nullobj, ref nullobj,
                    ref nullobj, ref nullobj, ref nullobj, ref nullobj,
                    ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj,
                    ref nullobj, ref nullobj, ref nullobj);

                doc.PrintPreview();
            }
            #endregion

            #region print excel file
            else if (Path.GetExtension(Path_new).ToLower().Equals(".xls")
                || Path.GetExtension(Path_new).ToLower().Equals(".xlsx"))
            {
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                ExcelApp.Visible = true;
                ExcelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook WBook = ExcelApp.Workbooks.Open(Path_new, 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);
                object obj = true;
                WBook.PrintPreview(obj);
                WBook.Close(false, Missing.Value, Missing.Value);
                ExcelApp.Quit();
            }
            #endregion

            #region print .pdf file
            else if (Path.GetExtension(Path_new).ToLower().Equals(".pdf"))
            {
                System.Diagnostics.Process objProcess = new System.Diagnostics.Process();
                objProcess.StartInfo.FileName = Path_new; //file to print
                objProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                objProcess.StartInfo.UseShellExecute = true;
                objProcess.StartInfo.CreateNoWindow = false;
                objProcess.StartInfo.ErrorDialog = false;
                objProcess.StartInfo.Verb = "print";
                objProcess.Start();
            }
            #endregion

            #region print html file
            else if (Path.GetExtension(Path_new).ToLower().Equals(".htm")
                || Path.GetExtension(Path_new).ToLower().Equals(".html"))
            {
                WebBrowser browser = new WebBrowser();
                browser.DocumentText = read.ReadToEnd();
                while (browser.ReadyState != WebBrowserReadyState.Complete)
                {
                    Application.DoEvents();
                }
                browser.Parent = this;
                browser.ShowPrintPreviewDialog();
                browser.Dispose();
            }
            #endregion

            else if (print.ShowDialog() == DialogResult.OK)
            {
                PrintPreviewDialog ppd = new PrintPreviewDialog();
                ppd.Document = thePrint;
                ((Form)ppd).WindowState = FormWindowState.Maximized;
                ppd.ShowDialog();
                //thePrint.Print();
            }
        }
Пример #37
0
        // Record the probability of predicted class & the expected class
        private void btnRecordProbabilities_Click(object sender, EventArgs e)
        {
            int noOfSheets = 101;

            //string writeFileLocation = txtSingleFileWriteLocation.Text;
            // Reading Excel Sheet
            Microsoft.Office.Interop.Excel.Application xlApp           = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook    xlReadWorkBook  = xlApp.Workbooks.Open(txtSingleFileReadLocation.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet   xlReadWorkSheet = null;

            // Origional File
            Microsoft.Office.Interop.Excel.Workbook  xlReadWorkBookOrigionalFile  = xlApp.Workbooks.Open(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\Orgional\Origional.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet xlReadWorkSheetOrigionalFile = null;

            //Writing Excel Sheet
            Microsoft.Office.Interop.Excel.Workbook  xlWriteWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWriteWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlWriteWorkBook  = xlApp.Workbooks.Add(misValue);
            xlWriteWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWriteWorkBook.Worksheets.get_Item(1);

            xlWriteWorkSheet.Cells[1, 1] = "OrigionalClass";
            xlWriteWorkSheet.Cells[1, 2] = "PredictedClass";
            xlWriteWorkSheet.Cells[1, 3] = "IsCorrectOutputPredicted";
            xlWriteWorkSheet.Cells[1, 4] = "PredictedClassProbability";
            xlWriteWorkSheet.Cells[1, 5] = "Expected(Origional)ClassProbability";

            int    writeRowCount = 2;
            string origionalPredictedClass;
            string currentMRPredictedClass;
            string probabilityOfCurrentMRPredictedClass;
            string probabilitiesOfCurrentMRClasses;
            int    rw = 0;
            int    cl = 0;

            Microsoft.Office.Interop.Excel.Range range;
            Microsoft.Office.Interop.Excel.Range rangeOrigionalFile;
            double probabilityOfOrigionalPredictedClassInCurrentMR;

            string[] predictedProbabilitesArrary;
            int      rowToBeProcessed = Convert.ToInt16(txtInstanceNo.Text);

            for (int i = 2; i <= noOfSheets; i++)
            {
                xlReadWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBook.Worksheets.get_Item(i);
                xlReadWorkSheetOrigionalFile = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBookOrigionalFile.Worksheets.get_Item(i); // Orgional File
                rangeOrigionalFile           = xlReadWorkSheetOrigionalFile.UsedRange;
                range = xlReadWorkSheet.UsedRange;
                rw    = range.Rows.Count;
                cl    = range.Columns.Count;
                origionalPredictedClass = (string)(rangeOrigionalFile.Cells[rowToBeProcessed, 3] as Microsoft.Office.Interop.Excel.Range).Value2; // Origional File Predicted Class

                currentMRPredictedClass = (string)(range.Cells[3, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
                probabilityOfCurrentMRPredictedClass = (string)(range.Cells[rowToBeProcessed, 5] as Microsoft.Office.Interop.Excel.Range).Value2;
                probabilitiesOfCurrentMRClasses      = (string)(range.Cells[rowToBeProcessed, 4] as Microsoft.Office.Interop.Excel.Range).Value2;
                predictedProbabilitesArrary          = probabilitiesOfCurrentMRClasses.Replace("\n", "").Replace("[", "").Replace("]", "").Split(' ');
                predictedProbabilitesArrary          = predictedProbabilitesArrary.Where(x => !string.IsNullOrEmpty(x)).ToArray();
                //Write in Excel Sheet, this library has start index from 1 (so we have to add 1)
                probabilityOfOrigionalPredictedClassInCurrentMR = double.Parse(predictedProbabilitesArrary[Convert.ToInt16(origionalPredictedClass)], CultureInfo.InvariantCulture); // Get probability against predicted class in orgional file
                xlWriteWorkSheet.Cells[writeRowCount, 1]        = origionalPredictedClass;
                xlWriteWorkSheet.Cells[writeRowCount, 2]        = currentMRPredictedClass;
                xlWriteWorkSheet.Cells[writeRowCount, 3]        = origionalPredictedClass == currentMRPredictedClass ? 1 : 0;
                xlWriteWorkSheet.Cells[writeRowCount, 4]        = probabilityOfCurrentMRPredictedClass;
                xlWriteWorkSheet.Cells[writeRowCount, 5]        = probabilityOfOrigionalPredictedClassInCurrentMR;
                writeRowCount = writeRowCount + 1;
            }
            xlWriteWorkBook.Close(true, misValue, misValue);
            xlReadWorkBook.Close(true, null, null);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlReadWorkSheet);
            Marshal.ReleaseComObject(xlReadWorkBook);
            Marshal.ReleaseComObject(xlReadWorkSheetOrigionalFile);
            Marshal.ReleaseComObject(xlReadWorkBookOrigionalFile);
            Marshal.ReleaseComObject(xlWriteWorkSheet);
            Marshal.ReleaseComObject(xlWriteWorkBook);
            Marshal.ReleaseComObject(xlApp);

            MessageBox.Show("Process Completed Successfully.");
        }
Пример #38
0
        private void WriteToExcel(DataTable dtNewOnly, DataTable dtOldOnly, DataTable dtNewMatched, DataTable dtOldMatched,
                                  DataTable dtNewPerfectMatch, DataTable dtOldPerfectMatch,
                                  List<Column> allColumns, List<Column> ignoredColumns, List<int> matchingColumns,
                                  string report, string portfolio, string fileName, List<List<int>> unmatchedRows,
                                  double tolerance)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;

            object missing = Type.Missing;
            Microsoft.Office.Interop.Excel.Worksheet ws = null;

            List<int> ignoredColumnNumbers = ignoredColumns.Select(x => x.Id).ToList();

            string reportName = Path.GetFileNameWithoutExtension(report);

            try
            {
                excel = new Microsoft.Office.Interop.Excel.Application();

                bool fileExists = File.Exists(fileName);

                if (fileExists)
                {
                    wb = excel.Workbooks.Open(Filename: fileName);
                    ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
                }
                else
                {
                    wb = excel.Workbooks.Add(missing);
                    ws = wb.Sheets.Add(After: (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[wb.Worksheets.Count]);
                    if (wb.Worksheets.Count > 1)
                    {
                        for (int i = wb.Worksheets.Count - 1; i > 0; i--)
                        {
                            ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Delete();
                        }
                    }
                }

                WriteOverview(ws, fileExists, report, tolerance, ignoredColumns,
                              dtNewMatched.Rows.Count + dtNewPerfectMatch.Rows.Count,
                              dtNewMatched.Rows.Count, dtNewOnly.Rows.Count, dtOldOnly.Rows.Count);

                ws = fileExists ? wb.Sheets[ws.Index + 1]
                                : wb.Sheets.Add(After: ws);

                WriteDataTableToWorksheet(ws, dtOldOnly, "Old Only", reportName, portfolio, allColumns, matchingColumns,
                                          ignoredColumnNumbers, System.Drawing.Color.FromArgb(0, 255, 0), fileExists);

                ws = fileExists ? wb.Sheets[ws.Index + 1]
                                : wb.Sheets.Add(After: ws);

                WriteDataTableToWorksheet(ws, dtNewOnly, "New Only", reportName, portfolio, allColumns, matchingColumns,
                                          ignoredColumnNumbers, System.Drawing.Color.Aqua, fileExists);

                ws = fileExists ? wb.Sheets[ws.Index + 1]
                                : wb.Sheets.Add(After: ws);

                WriteDataTablesToWorksheet(ws, dtNewMatched, dtOldMatched, reportName, portfolio, allColumns, matchingColumns,
                                           ignoredColumnNumbers, unmatchedRows, fileExists);

                ws = fileExists ? wb.Sheets[ws.Index + 1]
                                : wb.Sheets.Add(After: ws);

                WriteDataTablesToWorksheet(ws, dtNewPerfectMatch, dtOldPerfectMatch, reportName, portfolio, allColumns,
                                           matchingColumns, ignoredColumnNumbers, null, fileExists);

                ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.Sheets[1]).Select(missing);

                if (fileExists)
                {
                    wb.Save();
                }
                else
                {
                    wb.SaveAs(
                        Filename: fileName,
                        FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
                        AccessMode: Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange
                    );
                }

                wb.Close();
                excel.Quit();
            }
            catch (COMException ex)
            {
                if (wb != null) wb.Close(SaveChanges: false);
                if (excel != null) excel.Quit();
                throw new System.ArgumentException("Error accessing Microsoft Excel, make sure it is already installed!\n\n"
                    + ex.ToString());
            }
            catch (Exception ex)
            {
                if (wb != null) wb.Close(SaveChanges: false);
                if (excel != null) excel.Quit();
                throw new System.ArgumentException("Error: " + ex.ToString());
            }
            finally
            {
                if (excel != null) excel.Quit();
            }
        }
Пример #39
0
        // Hàm xử lý xuất file Excel sử dụng thư viện Microsoft
        private void ExportToExcel(DataGridView dgView)
        {
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            try
            {
                // instantiating the excel application class
                object misValue = System.Reflection.Missing.Value;
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook  currentWorkbook  = excelApp.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
                currentWorksheet.Columns.ColumnWidth = 18;
                if (dgView.Rows.Count > 0)
                {
                    currentWorksheet.Cells[4, 1] = "Mã Stockist";
                    currentWorksheet.Cells[4, 2] = "____________";
                    currentWorksheet.Cells[5, 1] = "Địa chỉ Stockist";
                    currentWorksheet.Cells[5, 2] = "____________";
                    currentWorksheet.Cells[6, 1] = "Ngày Tạo:";
                    currentWorksheet.Cells[6, 2] = txtNgayLap.Text;

                    Microsoft.Office.Interop.Excel.Range mm = currentWorksheet.get_Range("C1", "G1");
                    mm.MergeCells          = true;
                    mm.Font.Bold           = true;
                    mm.Font.Size           = 20;
                    mm.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                    mm.Cells[1, 1]         = "ĐƠN ĐẶT HÀNG STOCKIST";
                    //currentWorksheet.Cells[1, 7] = "ĐƠN ĐẶT HÀNG STOCKIST";

                    mm            = currentWorksheet.get_Range("D3", "G6");
                    mm.MergeCells = true;
                    mm.WrapText   = true;
                    mm.Cells[Type.Missing, Type.Missing] = "Công ty TNHH Synergy Việt Nam; Số 27 - tổ 41, Phường Trung Hoà; Quận Cầu Giấy, Hà Nội, Việt Nam; Tel: 84-4 35562535 - Fax: 84-4 35562356";

                    currentWorksheet.Cells[2, 6] = "VIET NAM";
                    //currentWorksheet.Cells[3, 7] = "Công ty TNHH Synergy Việt Nam";
                    //currentWorksheet.Cells[4, 7] = "Số 27 - tổ 41, Phường Trung Hoà";
                    //currentWorksheet.Cells[5, 7] = "Quận Cầu Giấy, Hà Nội, Việt Nam";
                    //currentWorksheet.Cells[6, 7] = "Tel: 84-4 35562535   - Fax: 84-4 35562356";
                    int i = 1;
                    foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
                    {
                        // Excel work sheet indexing starts with 1
                        currentWorksheet.Cells[7, i] = dgviewColumn.HeaderText.ToString().ToUpper();
                        ++i;
                    }
                    Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A7", "G7");
                    headerColumnRange.Font.Bold  = true;
                    headerColumnRange.Font.Color = 0x000000;


                    int rowIndex = 0;
                    for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
                    {
                        DataGridViewRow dgRow = dgView.Rows[rowIndex];
                        for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
                        {
                            currentWorksheet.Cells[rowIndex + 8, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
                        }
                    }
                    Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A8", "G" + (rowIndex + 1).ToString());
                    fullTextRange.WrapText            = true;                                                 //Tự động xuống dòng khi chữ quá dài
                    fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //Canh lề trái cho chữ

                    for (int row = 0; row < 3; row++)
                    {
                        string cell1, cell2;
                        cell1 = "A" + (rowIndex + 8 + row).ToString();
                        cell2 = "F" + (rowIndex + 8 + row).ToString();
                        Microsoft.Office.Interop.Excel.Range MergeRowRange = currentWorksheet.get_Range(cell1, cell2);
                        MergeRowRange.MergeCells          = true;
                        MergeRowRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                    }
                    currentWorksheet.Cells[rowIndex + 8, 1] = "Tổng tiền trước chiết khấu:";
                    currentWorksheet.Cells[rowIndex + 8, 7] = CalcMoney(dgView);

                    currentWorksheet.Cells[rowIndex + 9, 1] = "Chiết khấu:";
                    currentWorksheet.Cells[rowIndex + 9, 7] = string.Format("{0:#,0.##}", ChietKhau);

                    currentWorksheet.Cells[rowIndex + 10, 1] = "Tổng tiền thanh toán:";
                    currentWorksheet.Cells[rowIndex + 10, 7] = string.Format("{0:#,0.##}", CalcMoney(dgView) * (1.0 - (ChietKhau / 10)));

                    Microsoft.Office.Interop.Excel.Range AllRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 10).ToString());
                    AllRange.Columns.AutoFit();
                    AllRange = currentWorksheet.get_Range("A7", "G" + (rowIndex + 10).ToString());
                    AllRange.Borders.Color = 0x000000;
                    try
                    {
                        currentWorksheet.Shapes.AddPicture(@"D:\Subject\2013-HKI-Nhap Mon Cong Nghe Phan Mem\DoAn\SVN\Database\logo.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 140, 40);
                        currentWorksheet.Shapes.AddPicture(@"D:\Subject\2013-HKI-Nhap Mon Cong Nghe Phan Mem\DoAn\SVN\Database\flag.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 380, 27, 23, 15);
                    }
                    catch
                    {
                        MessageBox.Show("Không tìm thấy tập tin ảnh");
                    }
                }
                else
                {
                    MessageBox.Show("Không có dữ liệu để xuất", "Xuất file", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                    //string timeStamp = DateTime.Now.ToString("s");
                    //timeStamp = timeStamp.Replace(':', '-');
                    //timeStamp = timeStamp.Replace("T", "__");
                    //currentWorksheet.Cells[1, 1] = timeStamp;
                    //currentWorksheet.Cells[1, 2] = "No error occured";
                }
                using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
                {
                    exportSaveFileDialog.Title  = "Select Excel File";
                    exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";

                    if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
                    {
                        string fullFileName = exportSaveFileDialog.FileName;
                        // currentWorkbook.SaveCopyAs(fullFileName);
                        // indicating that we already saved the workbook, otherwise call to Quit() will pop up
                        // the save file dialogue box

                        currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
                        currentWorkbook.Saved = true;
                        MessageBox.Show("Xuất file thành công", "Xuất file Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (excelApp != null)
                {
                    excelApp.Quit();
                }
            }
        }
Пример #40
0
 private void Form1_Load(object sender, EventArgs e)
 {
     string[] symbol = new string[5] { "A", "B", "C", "D", "E" };
     OpenFileDialog openFileDialog1 = new OpenFileDialog();
     openFileDialog1.ShowDialog();
     Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
     Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog1.FileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
     Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
     ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
     for (int i = 1; i < 201; i++)
         {
         for (int j = 1; j < 6; j++)
         {
             Microsoft.Office.Interop.Excel.Range range = ObjWorkSheet.get_Range(symbol[j-1] + i.ToString(), symbol[j - 1] + i.ToString());
             education[i - 1, j - 1] = Double.Parse(range.Text.ToString());
         }
         }
      ObjExcel.Quit();
 }
Пример #41
0
        private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
        {
            int intRowCounter;
            int intRowNumberOfRecords;
            int intColumnCounter;
            int intColumnNumberOfRecords;

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

            try
            {
                expExportToExcel.IsExpanded = false;

                worksheet = workbook.ActiveSheet;

                worksheet.Name = "OpenOrders";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                intRowNumberOfRecords    = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Rows.Count;
                intColumnNumberOfRecords = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Columns.Count;

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

                    cellColumnIndex++;
                }

                cellRowIndex++;
                cellColumnIndex = 1;

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

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

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

                saveDialog.ShowDialog();

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

                MessageBox.Show(ex.ToString());
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
Пример #42
0
        /// <summary>
        /// 读取指定人员某时段的拜访汇总报表数据,如果是部门管理,获取所有人的employeeID包含子部门
        /// </summary>
        /// <param name="employeeId">人员ID</param>
        /// <returns></returns>
        public string DownloadAllReportDate(string employeeId, string startDate, string endDate, string downloadType = "1")
        {
            string result = "", sql = "", date1 = "", date2 = "", fileName = "", savePath = "";

            result = "<GetData>" +
                     "<Result>False</Result>" +
                     "<Description></Description>" +
                     "<DataRows></DataRows>" +
                     "</GetData>";
            WorkShip workShip = new WorkShip();

            employeeId = workShip.GetAllMemberIDsByLeaderID(employeeId).Replace("|", "','");
            //sql = $"  Select t3.FName As EmployeeName, t1.FEmployeeID As EmployeeID,t2.FName As InstitutionName , sum(Case FScheduleID When '4484030a-28d1-4e5e-ba72-6655f1cb2898' Then 1 Else 0 End) AS UnplanedCallCount,  Sum(1) AS CallCount,SUM(ISNULL(DATEDIFF(mi, t1.FStartTime, t1.FEndTime), 0)) AS TimeSpan  From CallActivity t1 Left Join t_Items t2 On t1.FInstitutionID = t2.FID  Left Join t_Items t3 On t1.FEmployeeID = t3.FID   Where FDate between '{startDate}' and  '{endDate}' and FEmployeeID In('{employeeIds}') Group by t3.FName,t2.FName,t1.FEmployeeID  Order by CallCount Desc,TimeSpan Desc,FEmployeeID desc";

            if (downloadType == "1")
            {
                sql = $@"Select   Isnull(t4.FName,'') As  姓名
                ,FSubject as 主题,t2.FName as 拜访机构,FClientID as 客户 ,FConcept as 传递理念,t6.FName as 产品名称,FActivity as 总结 , ISNULL(t1.FType, '') as 拜访类型  , (Left(CONVERT(varchar(100), t1.FStartTime, 120), 16)) As 日期 From[CallActivity] t1 Left Join t_Items t2
                On t1.FInstitutionID = t2.FID Left Join t_Items t4 On t1.FEmployeeID = t4.FID left join t_Items t5 on t1.FInstitutionID = t5.FName left join t_Items t6 on t1.FProductID = t6.FID  where  t1.FEmployeeID in ('{employeeId}')  and  FDate between '{startDate}' and  '{endDate}'   Order by t4.FName Desc";
            }
            else if (downloadType == "2")
            {
                sql = $@"Select [FDept_2] as 销售总部
                      ,[FDept_3] as 大区
                      ,[FDept_4] as 省区
                      ,ISNULL([FDept_5],'') as  地区,Isnull(t2.FName,'') As  姓名,t1.FSignInAddress 签到地址, FSignInTime 签到时间,FSignOutTime 签退时间,FRemark 备注,[FDistance]  as 签到签退距离
                      ,[FType] as 类型
                      ,[FWeek] as 周
                      ,[FMonth] as 月份

                From [yaodaibao].[dbo].[RouteData_His] t1 Left Join t_Items t2         
									      On t1.FInstitutionID = t2.FID and  t1.FEmployeeID = t2.FID
										  and   t1.FInstitutionID = t2.FName  where  t1.FEmployeeID in ('{employeeId}')  and  FSignInTime between '{startDate}' and  '{endDate}'   Order by t2.FName Desc"                                        ;
            }
            SQLServerHelper runner = new SQLServerHelper();
            //sql = string.Format(sql, date1, date2, employeeId);
            DataTable dt = runner.ExecuteSql(sql);

            //如果没有数据返回错误
            if (dt.Rows.Count == 0)
            {
                result = @"{""GetData"":{""Result"":""False"",""DataRow"":{""FileURL"":""无数据可供下载.""}}}";
                return(result);
            }

            fileName = Guid.NewGuid().ToString().Replace("-", "");
            try
            {
                //移除列
                // dt.Columns.Remove("日期");
                var excel = new Microsoft.Office.Interop.Excel.Application
                {
                    DisplayAlerts = false
                };
                //生成一个新的工作薄
                var excelworkBook = excel.Workbooks.Add(Type.Missing);
                var excelSheet    = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                //获得表的行,列数目
                int row_num    = dt.Rows.Count;
                int column_num = dt.Columns.Count;
                //生成一个二维数组
                object[,] dataArry = new object[row_num, column_num];
                object[,] headArry = new object[1, column_num];
                //把表中的数据放到数组中
                for (int i = 0; i < row_num; i++)
                {
                    for (int j = 0; j < column_num; j++)
                    {
                        dataArry[i, j] = dt.Rows[i][j].ToString();
                    }
                }

                for (int j = 0; j < column_num; j++)
                {
                    headArry[0, j] = dt.Columns[j].ColumnName.ToString();
                }
                excel.Range[excel.Cells[1, 1], excel.Cells[1, column_num]].Value = headArry;
                //把数组中的数据放到Excel中
                excel.Range[excel.Cells[2, 1], excel.Cells[row_num + 1, column_num]].Value = dataArry;
                string path     = System.Configuration.ConfigurationManager.AppSettings["Path"];
                string fullpath = System.Web.HttpContext.Current.Server.MapPath(path);

                savePath = fullpath + "\\" + fileName + ".xlsx";

                excelworkBook.SaveAs(savePath);
                excelworkBook.Close();
                excel.Quit();
            }
            catch (Exception err)
            {
                result = $@"{{""GetData"":{{ ""Result"":""False"",""Description"":""{ err.Message}"",""DataRow"":"""" }} }}";

                return(result);
            }
            string url = "http://ydb.tenrypharm.com:6060/Files/" + fileName + ".xlsx";

            result = $@"{{""GetData"":{{ ""Result"":""True"",""DataRow"":{{""FileURL"":""{url}""}} }} }}";

            return(result);
        }
Пример #43
0
        private void ReportMonthExpectedExecution()
        {            
            SqlCommand comm = new SqlCommand("SELECT PFMName, PartnerType, FinPositionEPL, PlanSumm, FactSummOnDate, PlanSummOnDate, ExpectedMonthExec, AbsDeviation, RelativeDeviation FROM udf_get_MonthExpectedExecution(@date)", _connection);
            comm.CommandType = CommandType.Text;
            comm.Parameters.Add("@date", SqlDbType.Date).Direction = ParameterDirection.Input;
            comm.Parameters["@date"].Value = dateTimePicker1.Value.Date;

            try
            {
                if (_connection.State != System.Data.ConnectionState.Open)
                    _connection.Open();

                using (SqlDataReader reader = comm.ExecuteReader())
                {
                    //if (ExcelApp == null)
                    Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                    ExcelApp.Workbooks.Add(TemplatePath + "MonthExpectedExecution.xlt");
                    Microsoft.Office.Interop.Excel.Worksheet ReportWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Worksheets["First"];

                    Dictionary<string, string> labelDict = new Dictionary<string, string>();
                    labelDict.Add("#header", "Ожидаемое исполнение месяца на " + dateTimePicker1.Value.ToShortDateString());
                    labelDict.Add("#planmonth", "План поступлений/платежей на " + dateTimePicker1.Value.ToString("MM.yyyy"));
                    labelDict.Add("#factondate", "Факт " + "01." + dateTimePicker1.Value.ToString("MM") + " - " + dateTimePicker1.Value.ToString("dd.MM"));
                    labelDict.Add("#planondate", "План " + dateTimePicker1.Value.AddDays(1).ToString("dd.MM") + " - " + DateTime.DaysInMonth(dateTimePicker1.Value.Year, dateTimePicker1.Value.Month) + "." + dateTimePicker1.Value.ToString("MM"));

                    string error;

                    if (!ReportExcelUtil.ExportData2Report(ReportWorksheet, reader, labelDict, out error))
                    {
                        ExcelApp.Quit();
                        MessageBox.Show(error, "Ошибка");
                    }
                    else
                    {
                        ExcelApp.Visible = true;
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }            
        }
Пример #44
0
        public static string DataTableToExcel(DataTable dt)
        {
            string directoryName = Path.Combine(Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName, "ExcelFiles");


            if (!Directory.Exists(directoryName))
            {
                Directory.CreateDirectory(directoryName);
            }
            string fileName = string.Format(@"{0}\{1}", directoryName, DateTime.Now.ToString("ddMMyyyyHHmmss") + ".xlsx");

            if (!File.Exists(fileName))
            {
                File.Create(fileName);
            }


            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel.Workbook    excelworkBook;
            Microsoft.Office.Interop.Excel.Worksheet   excelSheet;
            Microsoft.Office.Interop.Excel.Range       excelCellrange;

            excel           = new Microsoft.Office.Interop.Excel.Application();
            excelworkBook   = excel.Workbooks.Add(Type.Missing);
            excelSheet      = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
            excelSheet.Name = "Test work sheet";


            // loop through each row and add values to our sheet
            int rowcount = 1;

            foreach (DataRow datarow in dt.Rows)
            {
                rowcount += 1;
                for (int i = 1; i <= dt.Columns.Count; i++)
                {
                    // on the first iteration we add the column headers
                    if (rowcount == 2)
                    {
                        excelSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                    }

                    excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
                }
            }

            // now we resize the columns
            excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dt.Columns.Count]];
            excelCellrange.EntireColumn.AutoFit();
            //Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
            //border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            //border.Weight = 2d;


            // excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dt.Columns.Count]];
            // FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);


            //now save the workbook and exit Excel


            excelworkBook.SaveAs(fileName);
            excelworkBook.Close();
            excel.Quit();

            return(fileName);
        }
Пример #45
0
        /// <summary>
        /// FUNCTION FOR EXPORT TO EXCEL
        /// </summary>2
        /// <param name="dataTable"></param>
        /// <param name="worksheetName"></param>
        /// <param name="saveAsLocation"></param>
        /// <returns></returns>
        public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel.Workbook excelworkBook;
            Microsoft.Office.Interop.Excel.Worksheet excelSheet;
            Microsoft.Office.Interop.Excel.Range excelCellrange;

            try
            {
                // Start Excel and get Application object.
                excel = new Microsoft.Office.Interop.Excel.Application();

                // for making Excel visible
                excel.Visible = false;
                excel.DisplayAlerts = false;

                // Creation a new Workbook
                excelworkBook = excel.Workbooks.Add(Type.Missing);

                // Workk sheet
                excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                excelSheet.Name = worksheetName;

                //excelSheet.Cells[1, 1] = ReporType;
                //excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

                // loop through each row and add values to our sheet
                int rowcount = 1;

                foreach (DataRow datarow in dataTable.Rows)
                {
                    rowcount += 1;
                    for (int i = 1; i <= dataTable.Columns.Count; i++)
                    {
                        // on the first iteration we add the column headers
                        if (rowcount == 2)
                        {
                            excelSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
                           // excelSheet.Cells.Font.Color = System.Drawing.Color.Black;

                        }

                        excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

                        //for alternate rows
                        if (rowcount > 2)
                        {
                            if (i == dataTable.Columns.Count)
                            {
                                if (rowcount % 2 == 0)
                                {
                                    excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                                   // FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
                                }

                            }
                        }

                    }

                }

                // now we resize the columns
                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                excelCellrange.EntireColumn.AutoFit();
                Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
                border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                border.Weight = 2d;

                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
                //FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);

                //now save the workbook and exit Excel

                excelworkBook.SaveAs(saveAsLocation);;
                excelworkBook.Close();
                excel.Quit();
                //System.Windows.Forms.DialogResult DGres = System.Windows.Forms.MessageBox.Show("Excel generated succesfully." + Environment.NewLine + "Do you want us to open the application? ", "Excel Report", System.Windows.Forms.MessageBoxButtons.YesNo);

                //if (DGres == System.Windows.Forms.DialogResult.Yes)
                //{
                //    Process.Start(@saveAsLocation);
                //}

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Excel Report generation failed : " + ex.Message.ToString());
                return false;
            }
            finally
            {
                excelSheet = null;
                excelCellrange = null;
                excelworkBook = null;
            }
        }
Пример #46
0
        private void btn_Export_Click(object sender, EventArgs e)
        {
            string checkStr = "0"; string PathName = "";

            try
            {
                if (dataGridVieweachdoctorappoinmt.Rows.Count != 0)
                {
                    SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                    saveFileDialog1.Filter   = "Excel Files (*.xls)|*.xls";
                    saveFileDialog1.FileName = "Appointment Report Of Doctor(" + DateTime.Now.ToString("dd-MM-yy h.mm.ss tt") + ").xls";
                    if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        PathName = saveFileDialog1.FileName;
                        Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                        ExcelApp.Application.Workbooks.Add(Type.Missing);
                        ExcelApp.Columns.ColumnWidth = 20;
                        int count = dataGridVieweachdoctorappoinmt.Columns.Count;
                        ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, count]].Merge();
                        if (comboBoxdoctor.SelectedIndex == 0)
                        {
                            ExcelApp.Cells[1, 1] = "APPOINTMENT REPORT OF All DOCTOR";
                        }
                        else if (comboBoxdoctor.SelectedIndex > 0)
                        {
                            ExcelApp.Cells[1, 1] = "APPOINTMENT REPORT OF Dr." + drctid + "";
                        }
                        ExcelApp.Cells[1, 1].HorizontalAlignment = HorizontalAlignment.Center;
                        ExcelApp.Cells[1, 1].Font.Size           = 12;
                        ExcelApp.Cells[1, 1].Interior.Color      = Color.FromArgb(153, 204, 255);
                        ExcelApp.Columns.ColumnWidth             = 20;
                        ExcelApp.Cells[2, 1]                     = "From Date";
                        ExcelApp.Cells[2, 1].Font.Size           = 10;
                        ExcelApp.Cells[3, 1]                     = "To Date";
                        ExcelApp.Cells[3, 1].Font.Size           = 10;
                        ExcelApp.Cells[2, 2]                     = dateTimePickerappointeachdoctor1.Value.ToString("dd-MM-yyyy");
                        ExcelApp.Cells[2, 2].Font.Size           = 10;
                        ExcelApp.Cells[3, 2]                     = dateTimePickerappointeachdoctor2.Value.ToString("dd-MM-yyyy");
                        ExcelApp.Cells[3, 2].Font.Size           = 10;
                        ExcelApp.Cells[4, 1]                     = "Running Date";
                        ExcelApp.Cells[4, 1].Font.Size           = 10;
                        ExcelApp.Cells[4, 2]                     = DateTime.Now.ToString("dd-MM-yyyy");
                        ExcelApp.Cells[4, 2].Font.Size           = 10;
                        ExcelApp.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                        ExcelApp.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                        for (int i = 1; i < dataGridVieweachdoctorappoinmt.Columns.Count + 1; i++)
                        {
                            ExcelApp.Cells[5, i]                     = dataGridVieweachdoctorappoinmt.Columns[i - 1].HeaderText;
                            ExcelApp.Cells[5, i].ColumnWidth         = 25;
                            ExcelApp.Cells[5, i].EntireRow.Font.Bold = true;
                            ExcelApp.Cells[5, i].Interior.Color      = Color.FromArgb(0, 102, 204);
                            ExcelApp.Cells[5, i].Font.Size           = 10;
                            ExcelApp.Cells[5, i].Font.Name           = "Arial";
                            ExcelApp.Cells[5, i].Font.Color          = Color.FromArgb(255, 255, 255);
                            ExcelApp.Cells[5, i].Interior.Color      = Color.FromArgb(0, 102, 204);
                        }
                        for (int i = 0; i <= dataGridVieweachdoctorappoinmt.Rows.Count; i++)
                        {
                            try
                            {
                                for (int j = 0; j < dataGridVieweachdoctorappoinmt.Columns.Count; j++)
                                {
                                    ExcelApp.Cells[i + 6, j + 1] = dataGridVieweachdoctorappoinmt.Rows[i].Cells[j].Value.ToString();
                                    ExcelApp.Cells[i + 6, j + 1].BorderAround(true);
                                    ExcelApp.Cells[i + 6, j + 1].Borders.Color = Color.FromArgb(0, 102, 204);
                                    ExcelApp.Cells[i + 6, j + 1].Font.Size     = 8;
                                }
                            }
                            catch
                            {
                            }
                        }
                        ExcelApp.ActiveWorkbook.SaveAs(PathName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                        ExcelApp.ActiveWorkbook.Saved = true;
                        ExcelApp.Quit();
                        checkStr = "1";
                        MessageBox.Show("Successfully Exported to Excel", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("No records found, Please change the date and try again!..", "No Records Found ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error !..", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
Пример #47
0
        public static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
        {
            //System.Data.DataTable dt = new System.Data.DataTable();

            if (dt == null) return;

            Microsoft.Office.Interop.Excel._Workbook oWB;
            Microsoft.Office.Interop.Excel.Series oSeries;
            //Microsoft.Office.Interop.Excel.Range oResizeRange;
            Microsoft.Office.Interop.Excel._Chart oChart;
            //String sMsg;
            //int iNumQtrs;
            GC.Collect();//ϵͳ����������

            //string filename = @"C:\Documents and Settings\tongxl\����\nnn.xls";
            //Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            //Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);

            Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);

            if (ep == null)
            {
                MsgBox.Show("�޷�����Excel���󣬿������Ļ���δ��װExcel");
                return;
            }

            ep.Visible = true;
            Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
            Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
            ws.UsedRange.Select();
            ws.UsedRange.Copy(System.Type.Missing);
            // wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
            int rowIndex = 1;
            int colIndex = 1;
            foreach (DataColumn col in dt.Columns)
            {
                ws.Cells[rowIndex, colIndex++] = col.ColumnName;
            }

            for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
            {
                DataRow row = dt.Rows[drvIndex];
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
                    colIndex++;
                }
            }

            oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
            oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);

            int rcount = dt.Rows.Count;
            int ccount = dt.Columns.Count;
            oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
             Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
             charttitle, Missing.Value, Missing.Value, Missing.Value);
            // oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);

            //string str = String.Empty;
            //for (int I = 1; I < 15; I++)

            //{
            //    str += I.ToString() + "\t";
            //}
            //try { oSeries.XValues = str; }
            //catch { }
            //  oSeries.HasDataLabels = true;
            //   string charttitle ,bool tableflag ,bool bl)
            if (tableflag == true)
                oChart.HasDataTable = true;
            else
                oChart.HasDataTable = false;
            oChart.PlotVisibleOnly = false;
            //

            Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
            Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
            Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            //axis.HasTitle = true;
            //axis.AxisTitle.Text = "Sales Figures";
            // axis.HasMinorGridlines=true;
            Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
               Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            //ax.HasTitle = true;
            //ax.AxisTitle.Text = "Sales Figures";
            ax.HasMajorGridlines = true;

            //  string filename = @"C:\Documents and Settings\tongxl\����\ccsb.xls";
            //  ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            try
            {
                wb.Saved = true;
                wb.SaveCopyAs(filename);
            }
            catch (Exception ex)
            {

                MsgBox.Show("�����ļ�ʱ����,�ļ����������򿪣�\n" + ex.Message);
            }
            ep.Quit();

            GC.Collect();//ǿ������
        }
Пример #48
0
        /// <summary>
        /// 转换excel 成PDF文档
        /// </summary>
        /// <param name="_lstrInputFile">原文件路径</param>
        /// <param name="_lstrOutFile">pdf文件输出路径</param>
        /// <returns>true 成功</returns>
        public bool ConverterToPdf(string _lstrInputFile, string _lstrOutFile)
        {
            if (!File.Exists(_lstrInputFile))
            {
                MessageBox.Show("源文件不存在", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return(false);
            }
            if (string.IsNullOrEmpty(_lstrOutFile) || _lstrOutFile.IndexOf("\\") < 0)
            {
                MessageBox.Show("请指定目标文件", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return(false);
            }
            if (!Directory.Exists(_lstrOutFile.Substring(0, _lstrOutFile.LastIndexOf("\\") + 1)))
            {
                MessageBox.Show("保存目录不存在", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return(false);
            }
            if (File.Exists(_lstrOutFile) && MessageBox.Show("文件【" + _lstrOutFile.Substring(_lstrOutFile.LastIndexOf("\\") + 1) + "】已存在,是否覆盖?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != System.Windows.Forms.DialogResult.Yes)
            {
                return(false);
            }
            _lstrOutFile = _lstrOutFile.Substring(0, _lstrOutFile.LastIndexOf("."));
            Microsoft.Office.Interop.Excel.Application lobjExcelApp       = null;
            Microsoft.Office.Interop.Excel.Workbooks   lobjExcelWorkBooks = null;
            Microsoft.Office.Interop.Excel.Workbook    lobjExcelWorkBook  = null;

            string lstrTemp    = string.Empty;
            object lobjMissing = System.Reflection.Missing.Value;

            try
            {
                lobjExcelApp         = new Microsoft.Office.Interop.Excel.Application();
                lobjExcelApp.Visible = true;
                lobjExcelWorkBooks   = lobjExcelApp.Workbooks;
                lobjExcelWorkBook    = lobjExcelWorkBooks.Open(_lstrInputFile, true, true, lobjMissing, lobjMissing, lobjMissing, true,
                                                               lobjMissing, lobjMissing, lobjMissing, lobjMissing, lobjMissing, false, lobjMissing, lobjMissing);

                //Microsoft.Office.Interop.Excel 12.0.0.0之后才有这函数
                //注释掉好像没问题  lstrTemp = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls" + (lobjExcelWorkBook.HasVBProject ? 'm' : 'x');
                //lstrTemp = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls";
                //注释掉好像没问题lobjExcelWorkBook.SaveAs(lstrTemp, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                //注释掉好像没问题   false, Type.Missing, Type.Missing, Type.Missing);
                //输出为PDF 第一个选项指定转出为PDF,还可以指定为XPS格式
                lobjExcelWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, _lstrOutFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, Type.Missing, false, Type.Missing, Type.Missing, false, Type.Missing);
                lobjExcelWorkBooks.Close();
                lobjExcelApp.Quit();
            }
            catch (Exception ex)
            {
                //其他日志操作;
                return(false);
            }
            finally
            {
                try
                {
                    if (lobjExcelWorkBook != null)
                    {
                        lobjExcelWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
                        Marshal.ReleaseComObject(lobjExcelWorkBook);
                        lobjExcelWorkBook = null;
                    }
                    if (lobjExcelWorkBooks != null)
                    {
                        lobjExcelWorkBooks.Close();
                        Marshal.ReleaseComObject(lobjExcelWorkBooks);
                        lobjExcelWorkBooks = null;
                    }
                    if (lobjExcelApp != null)
                    {
                        lobjExcelApp.Quit();
                        Marshal.ReleaseComObject(lobjExcelApp);
                        lobjExcelApp = null;
                    }
                }
                catch (Exception e)
                {
                }
                //主动激活垃圾回收器,主要是避免超大批量转文档时,内存占用过多,而垃圾回收器并不是时刻都在运行!
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return(true);
        }
Пример #49
0
        /// <summary>
        /// 打印
        /// </summary>
        private void printInfo()
        {
            Object oMissing = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Application m_objExcel = null;

            Microsoft.Office.Interop.Excel._Workbook m_objBook = null;

            Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;

            Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;

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

            try
            {
                m_objExcel = new Microsoft.Office.Interop.Excel.Application();

                DirectoryInfo Dir = new DirectoryInfo(".");

                m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Register.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;

                m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));

                // 号牌种类
                m_objRange = m_objSheet.get_Range("D11", oMissing);
                m_objRange.Value = this.category.Text;

                // 号牌号码
                m_objRange = m_objSheet.get_Range("H11", oMissing);
                m_objRange.Value = "辽B" + this.license.Text;

                // 品牌型号
                m_objRange = m_objSheet.get_Range("D12", oMissing);
                m_objRange.Value = this.brand.Text;

                //车辆识别代号
                m_objRange = m_objSheet.get_Range("H12", oMissing);
                m_objRange.Value = this.vin.Text;

                // 机动车所有人姓名/名称
                m_objRange = m_objSheet.get_Range("E3", oMissing);
                m_objRange.Value = this.ownerName.Text;

                // 机动车所有人邮寄地址
                m_objRange = m_objSheet.get_Range("E4", oMissing);
                m_objRange.Value = this.ownerAddress.Text;

                // 机动车所有人邮政编码
                m_objRange = m_objSheet.get_Range("I3", oMissing);
                m_objRange.Value = this.ownerPostcode.Text;

                // 机动车所有人固定电话
                m_objRange = m_objSheet.get_Range("I5", oMissing);
                m_objRange.Value = this.ownerPhone.Text;

                // 机动车所有人移动电话
                m_objRange = m_objSheet.get_Range("E5", oMissing);
                m_objRange.Value = this.ownerMobile.Text;

                // 省(自治县、直辖市)
                m_objRange = m_objSheet.get_Range("D10", oMissing);
                m_objRange.Value = "                          " + this.province.Text + "                            " + this.department.Text;

                // 代理人姓名、名称
                m_objRange = m_objSheet.get_Range("E6", oMissing);
                m_objRange.Value = this.agentName.Text;

                // 代理人联系电话
                m_objRange = m_objSheet.get_Range("H6", oMissing);
                m_objRange.Value = this.agentPhone.Text;

                // 办理日期
                m_objRange = m_objSheet.get_Range("F18", oMissing);
                m_objRange.Value = DateTime.Today.Year + "        " + DateTime.Today.Month + "        " + DateTime.Today.Day ;

                m_objExcel.DisplayAlerts = false;
                m_objBook.Save();
                m_objSheet.PrintOut();
            }
            catch (Exception ex)
            {
                // 输出异常信息
                MessageBox.Show("打印失败,异常信息为:" + ex.Message);
            }
            finally
            {
                if (m_objBook != null)
                {
                    m_objBook.Close(oMissing, oMissing, oMissing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                }

                if (m_objExcel != null)
                {
                    m_objExcel.Workbooks.Close();
                    m_objExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                }

                m_objBook = null;
                m_objExcel = null;
                GC.Collect();
            }
        }
Пример #50
0
        private void ExportToExcel()
        {
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

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

                int cellRowIndex    = 2;
                int cellColumnIndex = 1;

                worksheet.Cells[1, 1]  = "ID";
                worksheet.Cells[1, 2]  = "PLAKA";
                worksheet.Cells[1, 3]  = "GİRİŞ SAATİ";
                worksheet.Cells[1, 4]  = "GİRİŞ TARİHİ";
                worksheet.Cells[1, 5]  = "GİRİŞ KM";
                worksheet.Cells[1, 6]  = "GELDİĞİ YER";
                worksheet.Cells[1, 7]  = "ÇIKIŞ SAATİ";
                worksheet.Cells[1, 8]  = "ÇIKIŞ TARİHİ";
                worksheet.Cells[1, 9]  = "ÇIKIŞ KM";
                worksheet.Cells[1, 10] = "GİTTİĞİ YER";
                worksheet.Cells[1, 11] = "ŞOFÖR ADI";

                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (j == 2)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 5);
                        }
                        else if (j == 3)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 10);
                        }
                        else if (j == 6)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 5);
                        }
                        else if (j == 7)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 10);
                        }
                        else
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel dosyaları (*.xlsx)|*.xlsx|Tüm Dosyalar (*.*)|*.*";
                saveDialog.FilterIndex = 2;

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Veriler başarıyla Excele aktarıldı.");
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
Пример #51
0
        /// <summary>
        /// 导出Excel 的方法
        /// </summary>
        private void tslExport_Excel(string fileName, DataGridView myDGV)
        {
            string         saveFileName = "";
            SaveFileDialog saveDialog   = new SaveFileDialog();

            saveDialog.DefaultExt = "xls";
            saveDialog.Filter     = "Excel文件|*.xls";
            saveDialog.FileName   = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
            {
                return;                                //被点了取消
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook  workbook  = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            //写入标题
            for (int i = 1; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            int s = 0;

            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value))
                {
                    for (int i = 1; i < myDGV.ColumnCount; i++)
                    {
                        worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                    s++;
                }
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
            rang.NumberFormat = "000000000000";

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开!\n");
                }
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
            MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
        }
Пример #52
0
        public string ExportCallReport(string xmlString)
        {
            string result = "", sql = "", date1 = "", date2 = "", employeeIds = "", employeeId = "", savePath = "", fileName = "";

            result = "<GetData>" +
                     "<Result>False</Result>" +
                     "<Description></Description>" +
                     "<DataRows></DataRows>" +
                     "</GetData>";
            date1 = DateTime.Now.ToString("yyyy-MM") + "-01";
            date2 = DateTime.Now.ToString("yyyy-MM-dd");

            XmlDocument doc = new XmlDocument();

            doc.LoadXml(xmlString);
            XmlNode node = doc.SelectSingleNode("GetData/BeginDate");

            if (node != null && node.InnerText.Trim().Length > 0)
            {
                date1 = node.InnerText.Trim();
            }
            node = doc.SelectSingleNode("GetData/EndDate");
            if (node != null && node.InnerText.Trim().Length > 0)
            {
                date2 = node.InnerText.Trim();
            }

            node = doc.SelectSingleNode("GetData/EmployeeID");//若为团队负责人,要读取其及直接下属的数据
            if (node != null && node.InnerText.Trim().Length > 0)
            {
                employeeId = node.InnerText.Trim();
                //WorkShip ws = new WorkShip();
                //employeeIds = ws.GetAllMemberIDsByLeaderID(employeeId);
            }

            node = doc.SelectSingleNode("GetData/EmployeeIDList");
            if (node != null && node.InnerText.Trim().Length > 0)
            {
                employeeIds = node.InnerText.Trim();
                if (employeeIds == "99")//查询其所有下属
                {
                    WorkShip ws = new WorkShip();
                    employeeIds = ws.GetAllMemberIDsByLeaderID(employeeId);
                }
                employeeIds = employeeIds.Replace("|", "','");
            }
            if (employeeIds.Length == 0)
            {
                employeeIds = employeeId;
            }

            sql = @"select Fdate as 日期, [FEmployeeName] as 姓名,
                        [RouteCount] as 签到次数,
                        [OKRouteCount] as 有效签到次数,
                        [CallCount]  as 拜访次数,
                        [UnPlanedCallCount] as 非计划拜访次数
                         from [dbo].[Route_Call_View]    Where FDate between '{0}' and  '{1}' and FEmployeeID In('{2}') order by  Fdate desc ,[RouteCount] desc";
            SQLServerHelper runner = new SQLServerHelper();

            sql = string.Format(sql, date1, date2, employeeIds);
            DataTable dt = runner.ExecuteSql(sql);

            //如果没有数据返回错误
            if (dt.Rows.Count == 0)
            {
                result = @"<GetData>" +
                         "<Result>False</Result>" +
                         "<DataRow><FileURL>" + "无数据可供下载." + "</FileURL></DataRow></GetData>";
                return(result);
            }

            fileName = Guid.NewGuid().ToString().Replace("-", "");
            try
            {
                //移除列
                dt.Columns.Remove("日期");
                var excel = new Microsoft.Office.Interop.Excel.Application
                {
                    DisplayAlerts = false
                };
                //生成一个新的工资薄
                var excelworkBook = excel.Workbooks.Add(Type.Missing);
                var excelSheet    = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                //获得表的行,列数目
                int row_num    = dt.Rows.Count;
                int column_num = dt.Columns.Count;
                //生成一个二维数组
                object[,] dataArry = new object[row_num, column_num];
                object[,] headArry = new object[1, column_num];
                //把表中的数据放到数组中
                for (int i = 0; i < row_num; i++)
                {
                    for (int j = 0; j < column_num; j++)
                    {
                        dataArry[i, j] = dt.Rows[i][j].ToString();
                    }
                }

                for (int j = 0; j < column_num; j++)
                {
                    headArry[0, j] = dt.Columns[j].ColumnName.ToString();
                }
                excel.Range[excel.Cells[1, 1], excel.Cells[1, column_num]].Value = headArry;
                //把数组中的数据放到Excel中
                excel.Range[excel.Cells[2, 1], excel.Cells[row_num + 1, column_num]].Value = dataArry;
                string path     = System.Configuration.ConfigurationManager.AppSettings["Path"];
                string fullpath = System.Web.HttpContext.Current.Server.MapPath(path);

                savePath = fullpath + "\\" + fileName + ".xlsx";

                excelworkBook.SaveAs(savePath);
                excelworkBook.Close();
                excel.Quit();
            }
            catch (Exception err)
            {
                result = "" + "<GetData>" +
                         "<Result>False</Result>" +
                         "<Description>" + err.Message + "</Description></GetData>";
                return(result);
            }
            string url = "http://ydb.tenrypharm.com:6060/Files/" + fileName + ".xlsx";

            result = @"<GetData>" +
                     "<Result>True</Result>" +
                     "<DataRow><FileURL>" + url + "</FileURL></DataRow></GetData>";
            return(result);
        }
Пример #53
0
        /// <summary>   
        /// 将DataGridView中的数据导入到Excel中
        /// </summary>   
        /// <param name="datagridview">DataGridView</param>   
        /// <param name="SheetName">Excel sheet title</param>   
        public static void DataGridView2Excel(System.Windows.Forms.DataGridView datagridview, string SheetName)
        {
            //string val;//add by cs 20120531
            int iRows = 0;
            int iCols = 0;
            int iTrueCols = 0;
            string filename = "";
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            if (wb.Worksheets.Count > 0)
            {
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            }
            else
            {
                wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            }
            // Add by cs  Strat 2010-12-14
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter = "Execl2007 files(*.xlsx)|*.xlsx";

            saveFileDialog.FilterIndex = 0;

            saveFileDialog.RestoreDirectory = true;

            saveFileDialog.CreatePrompt = true;

            saveFileDialog.Title = "Export Data To Excel!"; //Modify by cs 2010-12-14

            //saveFileDialog.ShowDialog(); // Mark by cs 2010-12-14

            // Add by cs  End 2010-12-14
            saveFileDialog.CreatePrompt = false;
            // saveFileDialog.OverwritePrompt = false;
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    filename = saveFileDialog.FileName.ToString();
                    if (ws != null)
                    {
                        if (SheetName.Trim() != "")
                        {
                            ws.Name = SheetName;
                        }
                        iRows = datagridview.Rows.Count;   //加上列头行
                        iTrueCols = datagridview.Columns.Count;  //包含隐藏的列,一共有多少列
                        //求列数,省略Visible = false的列
                        for (int i = 0; i < datagridview.Columns.Count; i++)
                        {
                            if (datagridview.Columns[i].Visible)
                                iCols++;
                        }
                        //string[,] dimArray = new string[iRows + 1, iCols];    // 需要修改string[iRows + 1, iCols]为string[iRows, iCols]
                        string[,] dimArray = new string[iRows + 1, iCols];
                        for (int j = 0, k = 0; j < iTrueCols; j++)
                        {
                            //省略Visible = false的列
                            if (datagridview.Columns[j].Visible)
                            {
                                dimArray[0, k] = datagridview.Columns[j].HeaderText;
                                k++;
                            }
                        }
                        // for (int i = 0; i < iRows; i++) 修改前
                        for (int i = 0; i < iRows; i++) // 修改后
                        {
                            for (int j = 0, k = 0; j < iTrueCols; j++)
                            {
                                //省略Visible = false的列
                                if (datagridview.Columns[j].Visible) //mark by cs 20110121
                                {
                                    if (datagridview.Rows[i].Cells[j].Value == null)
                                    {
                                        dimArray[i + 1, k] = "Null";
                                    }
                                    else
                                    {
                                        dimArray[i + 1, k] = datagridview.Rows[i].Cells[j].Value.ToString();
                                    }
                                    k++;
                                }
                            }
                        }
                        /* 修改前
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Value2 = dimArray;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Font.Size = 10.0;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).RowHeight = 14.25;
                         * */
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Value2 = dimArray;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).Font.Size = 10.0;
                        ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).RowHeight = 14.25;
                        for (int j = 0, k = 0; j < iTrueCols; j++)
                        {
                            //省略Visible = false的列
                            if (datagridview.Columns[j].Visible)
                            {
                                ws.get_Range(ws.Cells[1, k + 1], ws.Cells[1, k + 1]).ColumnWidth =
                                    (datagridview.Columns[j].Width / 8.4) > 255 ? 255 : (datagridview.Columns[j].Width / 8.4);
                                //ws.Columns.c = datagridview.Columns[j].Width;
                                k++;
                            }
                        }
                    }
                    // app.Visible = true;//Modify by cs 20101214
                    //app.Visible = false;
                    //app.Save(filename);//Add by cs 2010-12-14 保存
                    //app.ActiveWorkbook.Name = filename;
                    //app.ActiveWorkbook.Save();
                    wb.Close(true, filename, System.Reflection.Missing.Value);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    wb.Close(false, filename, System.Reflection.Missing.Value);
                }
                finally
                {
                    // wb.Close(true, AFileName, ObjMissing);
                    app.Quit();
                    app = null;
                    GC.Collect();
                }

            }
            //else if  (saveFileDialog.FileName.IndexOf(":") < 0) //被点了"取消"
            else
            {
                wb.Close(false, filename, System.Reflection.Missing.Value);
                app.Quit();
                app = null;
                GC.Collect();
                return;
            }
        }
Пример #54
0
        public string GerarFichas(string mes, string ano, List <System.Data.DataTable> data, List <string[]> colunas, String fileName)
        {
            string ret = "Relatório gerado com sucesso!";

            try
            {
                Microsoft.Office.Interop.Excel.Application xla    = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook    wb     = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                Microsoft.Office.Interop.Excel.Sheets      sheets = null;
                Microsoft.Office.Interop.Excel.Worksheet   ws     = null;

                if (data[6].Rows.Count > 0)
                {
                    #region Ficha6F

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha6F";

                    for (int i = 1; i <= colunas[6].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[6][i - 1];
                        ws.Cells[5, i] = "(" + i + ")";
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00";

                    for (int i = 0; i < data[6].Rows.Count; i++)
                    {
                        string data_formatada = data[6].Rows[i]["DT_DOC"].ToString();
                        ws.Cells[i + 6, 1]  = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
                        ws.Cells[i + 6, 2]  = data[6].Rows[i]["TIPO"];
                        ws.Cells[i + 6, 3]  = data[6].Rows[i]["SER"];
                        ws.Cells[i + 6, 4]  = data[6].Rows[i]["NUM_DOC"];
                        ws.Cells[i + 6, 5]  = data[6].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 6]  = data[6].Rows[i]["NUM_DECL_DESP_EXP"];
                        ws.Cells[i + 6, 7]  = data[6].Rows[i]["VL_DOC"];
                        ws.Cells[i + 6, 8]  = data[6].Rows[i]["VL_BC_ICMS"];
                        ws.Cells[i + 6, 9]  = data[6].Rows[i]["VL_ICMS"];
                        ws.Cells[i + 6, 10] = data[6].Rows[i]["PCT_CRED_OUT"];
                        ws.Cells[i + 6, 11] = data[6].Rows[i]["VL_CRED_OUT"];
                    }

                    for (int i = 1; i <= colunas[6].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Demonstrativo das Operações Não Geradoras de Crédito Acumulado";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[5].Rows.Count > 0)
                {
                    #region Ficha6E

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha6E";

                    for (int i = 1; i <= colunas[5].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[5][i - 1];

                        if (i == 11)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (9) * (10) / 100";
                        }
                        else if (i == 14)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (11) + (13)";
                        }
                        else
                        {
                            ws.Cells[5, i] = "(" + i + ")";
                        }

                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 14]).EntireColumn.NumberFormat = "#,##0.00";

                    for (int i = 0; i < data[5].Rows.Count; i++)
                    {
                        string data_formatada = data[5].Rows[i]["DT_DOC"].ToString();
                        ws.Cells[i + 6, 1]  = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
                        ws.Cells[i + 6, 2]  = data[5].Rows[i]["TIPO"];
                        ws.Cells[i + 6, 3]  = data[5].Rows[i]["SER"];
                        ws.Cells[i + 6, 4]  = data[5].Rows[i]["NUM_DOC"];
                        ws.Cells[i + 6, 5]  = data[5].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 6]  = data[5].Rows[i]["COD_ENQUADRAMENTO"];
                        ws.Cells[i + 6, 7]  = data[5].Rows[i]["VL_DOC"];
                        ws.Cells[i + 6, 8]  = data[5].Rows[i]["IVA"];
                        ws.Cells[i + 6, 9]  = data[5].Rows[i]["CUSTO_EST"];
                        ws.Cells[i + 6, 10] = data[5].Rows[i]["PMC"];
                        ws.Cells[i + 6, 11] = data[5].Rows[i]["CRED_EST_IMP"];
                        ws.Cells[i + 6, 12] = data[5].Rows[i]["PCT_CRED_OUT"];
                        ws.Cells[i + 6, 13] = data[5].Rows[i]["VL_CRED_OUT"];
                        ws.Cells[i + 6, 14] = data[5].Rows[i]["VL_CRED_ACUM_GER"];
                    }

                    for (int i = 1; i <= colunas[5].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Demais Casos";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[4].Rows.Count > 0)
                {
                    #region Ficha6D

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha6D";

                    for (int i = 1; i <= colunas[4].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[4][i - 1];

                        if (i == 12)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (10) * (11)";
                        }
                        else if (i == 16)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (14) + (15)";
                        }
                        else
                        {
                            ws.Cells[5, i] = "(" + i + ")";
                        }

                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 10]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 12]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 15]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";

                    for (int i = 0; i < data[4].Rows.Count; i++)
                    {
                        string data_formatada = data[4].Rows[i]["DT_DOC"].ToString();
                        ws.Cells[i + 6, 1]  = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
                        ws.Cells[i + 6, 2]  = data[4].Rows[i]["TIPO"];
                        ws.Cells[i + 6, 3]  = data[4].Rows[i]["SER"];
                        ws.Cells[i + 6, 4]  = data[4].Rows[i]["NUM_DOC"];
                        ws.Cells[i + 6, 5]  = data[4].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 6]  = data[4].Rows[i]["COD_ENQUADRAMENTO"];
                        ws.Cells[i + 6, 7]  = (Convert.ToBoolean(data[4].Rows[i]["COMPROV_OP"])) ? "Sim" : "Não";
                        ws.Cells[i + 6, 8]  = data[4].Rows[i]["VL_DOC"];
                        ws.Cells[i + 6, 9]  = data[4].Rows[i]["IVA"];
                        ws.Cells[i + 6, 10] = data[4].Rows[i]["CUSTO_EST"];
                        ws.Cells[i + 6, 11] = data[4].Rows[i]["PMC"];
                        ws.Cells[i + 6, 12] = data[4].Rows[i]["CRED_EST_IMP"];
                        ws.Cells[i + 6, 13] = data[4].Rows[i]["PCT_CRED_OUT"];
                        ws.Cells[i + 6, 14] = data[4].Rows[i]["VL_CRED_OUT"];
                        ws.Cells[i + 6, 15] = data[4].Rows[i]["VL_ICMS_COMPR"];
                        ws.Cells[i + 6, 16] = data[4].Rows[i]["VL_CRED_ACUM_GER"];
                    }

                    for (int i = 1; i <= colunas[4].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Zona Franca de Manaus";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[3].Rows.Count > 0)
                {
                    #region Ficha6B

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha6B";

                    for (int i = 1; i <= colunas[3].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[3][i - 1];

                        if (i == 13)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (11) * (12)";
                        }
                        else if (i == 16)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (13) + (15)";
                        }
                        else if (i == 17)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (16) - (9)";
                        }
                        else
                        {
                            ws.Cells[5, i] = "(" + i + ")";
                        }

                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00";


                    for (int i = 0; i < data[3].Rows.Count; i++)
                    {
                        string data_formatada = data[3].Rows[i]["DT_DOC"].ToString();
                        ws.Cells[i + 6, 1]  = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
                        ws.Cells[i + 6, 2]  = data[3].Rows[i]["TIPO"];
                        ws.Cells[i + 6, 3]  = data[3].Rows[i]["SER"];
                        ws.Cells[i + 6, 4]  = data[3].Rows[i]["NUM_DOC"];
                        ws.Cells[i + 6, 5]  = data[3].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 6]  = data[3].Rows[i]["COD_ENQUADRAMENTO"];
                        ws.Cells[i + 6, 7]  = data[3].Rows[i]["VL_DOC"];
                        ws.Cells[i + 6, 8]  = data[3].Rows[i]["VL_BC_ICMS"];
                        ws.Cells[i + 6, 9]  = data[3].Rows[i]["VL_ICMS"];
                        ws.Cells[i + 6, 10] = data[3].Rows[i]["IVA"];
                        ws.Cells[i + 6, 11] = data[3].Rows[i]["CUSTO_EST"];
                        ws.Cells[i + 6, 12] = data[3].Rows[i]["PMC"];
                        ws.Cells[i + 6, 13] = data[3].Rows[i]["CRED_EST_IMP"];
                        ws.Cells[i + 6, 14] = data[3].Rows[i]["PCT_CRED_OUT"];
                        ws.Cells[i + 6, 15] = data[3].Rows[i]["VL_CRED_OUT"];
                        ws.Cells[i + 6, 16] = data[3].Rows[i]["VL_TOTAL_ICMS"];
                        ws.Cells[i + 6, 17] = data[3].Rows[i]["VL_CRED_ACUM_GER"];
                    }

                    for (int i = 1; i <= colunas[3].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso II - Operações com Redução de Base de Cálculo";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[2].Rows.Count > 0)
                {
                    #region Ficha6A

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha6A";

                    for (int i = 1; i <= colunas[2].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[2][i - 1];

                        if (i == 13)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (11) * (12)";
                        }
                        else if (i == 16)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (13) + (15)";
                        }
                        else if (i == 17)
                        {
                            ws.Cells[5, i] = "(" + i + ") = (16) - (9)";
                        }
                        else
                        {
                            ws.Cells[5, i] = "(" + i + ")";
                        }

                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat  = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00";


                    for (int i = 0; i < data[2].Rows.Count; i++)
                    {
                        string data_formatada = data[2].Rows[i]["DT_DOC"].ToString();
                        ws.Cells[i + 6, 1]  = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
                        ws.Cells[i + 6, 2]  = data[2].Rows[i]["TIPO"];
                        ws.Cells[i + 6, 3]  = data[2].Rows[i]["SER"];
                        ws.Cells[i + 6, 4]  = data[2].Rows[i]["NUM_DOC"];
                        ws.Cells[i + 6, 5]  = data[2].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 6]  = data[2].Rows[i]["COD_ENQUADRAMENTO"];
                        ws.Cells[i + 6, 7]  = data[2].Rows[i]["VL_DOC"];
                        ws.Cells[i + 6, 8]  = data[2].Rows[i]["VL_BC_ICMS"];
                        ws.Cells[i + 6, 9]  = data[2].Rows[i]["VL_ICMS"];
                        ws.Cells[i + 6, 10] = data[2].Rows[i]["IVA"];
                        ws.Cells[i + 6, 11] = data[2].Rows[i]["CUSTO_EST"];
                        ws.Cells[i + 6, 12] = data[2].Rows[i]["PMC"];
                        ws.Cells[i + 6, 13] = data[2].Rows[i]["CRED_EST_IMP"];
                        ws.Cells[i + 6, 14] = data[2].Rows[i]["PCT_CRED_OUT"];
                        ws.Cells[i + 6, 15] = data[2].Rows[i]["VL_CRED_OUT"];
                        ws.Cells[i + 6, 16] = data[2].Rows[i]["VL_TOTAL_ICMS"];
                        ws.Cells[i + 6, 17] = data[2].Rows[i]["VL_CRED_ACUM_GER"];
                    }

                    for (int i = 1; i <= colunas[2].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso I - Operações com Aplicação de Alíquotas Diversificadas";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[1].Rows.Count > 0)
                {
                    #region Ficha5D

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha5D";

                    for (int i = 1; i <= colunas[1].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[1][i - 1];
                        ws.Cells[5, i] = "(" + i + ")";
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    for (int i = 0; i < data[1].Rows.Count; i++)
                    {
                        ws.Cells[i + 6, 1]  = data[1].Rows[i]["COD_ENQUADRAMENTO"];
                        ws.Cells[i + 6, 2]  = data[1].Rows[i]["COD_HIP_GER"];
                        ws.Cells[i + 6, 3]  = data[1].Rows[i]["ANEXO"];
                        ws.Cells[i + 6, 4]  = data[1].Rows[i]["ARTIGO"];
                        ws.Cells[i + 6, 5]  = data[1].Rows[i]["INCISO"];
                        ws.Cells[i + 6, 6]  = data[1].Rows[i]["ALINEA"];
                        ws.Cells[i + 6, 7]  = data[1].Rows[i]["PARAGRAFO"];
                        ws.Cells[i + 6, 8]  = data[1].Rows[i]["ITEM"];
                        ws.Cells[i + 6, 9]  = data[1].Rows[i]["LETRA"];
                        ws.Cells[i + 6, 10] = data[1].Rows[i]["OBS"];
                    }

                    for (int i = 1; i <= colunas[1].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Enquadramento Legal da Operação/Prestação Geradora do Crédito Acumulado do ICMS";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                if (data[0].Rows.Count > 0)
                {
                    #region Ficha5C

                    sheets  = wb.Sheets;
                    ws      = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
                    ws.Name = "Ficha5C";

                    for (int i = 1; i <= colunas[0].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat        = "@";
                        ws.Cells[4, i] = colunas[0][i - 1];
                        ws.Cells[5, i] = "(" + i + ")";
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold  = true;
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
                    }

                    for (int i = 0; i < data[0].Rows.Count; i++)
                    {
                        ws.Cells[i + 6, 1]  = data[0].Rows[i]["COD_PART"];
                        ws.Cells[i + 6, 2]  = data[0].Rows[i]["NOME"];
                        ws.Cells[i + 6, 3]  = data[0].Rows[i]["COD_PAIS"];
                        ws.Cells[i + 6, 4]  = data[0].Rows[i]["CNPJ"];
                        ws.Cells[i + 6, 5]  = data[0].Rows[i]["IE"];
                        ws.Cells[i + 6, 6]  = data[0].Rows[i]["END"];
                        ws.Cells[i + 6, 7]  = data[0].Rows[i]["NUM"];
                        ws.Cells[i + 6, 8]  = data[0].Rows[i]["COMPL"];
                        ws.Cells[i + 6, 9]  = data[0].Rows[i]["BAIRRO"];
                        ws.Cells[i + 6, 10] = data[0].Rows[i]["MUN"];
                        ws.Cells[i + 6, 11] = data[0].Rows[i]["CEP"];
                        ws.Cells[i + 6, 12] = data[0].Rows[i]["UF"];
                    }

                    for (int i = 1; i <= colunas[0].Length; i++)
                    {
                        ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
                    }

                    ws.Cells[1, 1] = "Cadastro de Participantes de Operações e Prestações";
                    ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold           = true;
                    ((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold           = true;

                    #endregion
                }

                wb.SaveAs(fileName, 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);
                wb.Close();
                xla.Quit();
            }
            catch (Exception ex)
            {
                ret = "Erro ao gerar relatório";
            }

            return(ret);
        }
Пример #55
0
        private void VerifyEmptyTabs(string fileName, int oldOnlyCount, int newOnlyCount, int diffCount, int matchCount)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;

            object missing = Type.Missing;
            Microsoft.Office.Interop.Excel.Worksheet ws = null;

            try
            {
                excel = new Microsoft.Office.Interop.Excel.Application();

                wb = excel.Workbooks.Open(Filename: fileName);
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

                excel.DisplayAlerts = false;
                if (matchCount == 0) wb.Worksheets[5].Delete();
                if (diffCount == 0) wb.Worksheets[4].Delete();
                if (newOnlyCount == 0) wb.Worksheets[3].Delete();
                if (oldOnlyCount == 0) wb.Worksheets[2].Delete();
                excel.DisplayAlerts = true;

                ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.Sheets[1]).Select(missing);

                wb.Save();

                wb.Close();
                excel.Quit();
            }
            catch (COMException ex)
            {
                wb.Close(SaveChanges: false);
                excel.Quit();
                throw new System.ArgumentException("Error accessing Excel: " + ex.ToString());
            }
            catch (Exception ex)
            {
                wb.Close(SaveChanges: false);
                excel.Quit();
                throw new System.ArgumentException("Error: " + ex.ToString());
            }
            finally
            {
                excel.Quit();
            }
        }
        private void btnOpen_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openfile = new OpenFileDialog();

            openfile.DefaultExt = ".xlsx";
            openfile.Filter     = "(.xlsx)|*.xlsx";
            //openfile.ShowDialog();

            var browsefile = openfile.ShowDialog();

            if (browsefile == true)
            {
                txtFilePath.Text = openfile.FileName;

                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                //Static File From Base Path...........
                //Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                //Dynamic File Using Uploader...........
                Microsoft.Office.Interop.Excel.Workbook  excelBook  = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
                Microsoft.Office.Interop.Excel.Range     excelRange = excelSheet.UsedRange;

                string strCellData = "";
                double douCellData;
                int    rowCnt = 0;
                int    colCnt = 0;

                DataTable dt = new DataTable();
                for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                {
                    string strColumn = "";
                    strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                    dt.Columns.Add(strColumn, typeof(string));
                }


                for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
                {
                    string strData = "";
                    for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                    {
                        try
                        {
                            strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                            strData    += strCellData + "|";
                        }
                        catch (Exception ex)
                        {
                            douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                            strData    += douCellData.ToString() + "|";
                        }
                    }
                    strData = strData.Remove(strData.Length - 1, 1);
                    dt.Rows.Add(strData.Split('|'));
                }

                dtGrid.ItemsSource = dt.DefaultView;

                excelBook.Close(true, null, null);
                excelApp.Quit();
            }
        }
Пример #57
0
        /// <summary>
        /// Метод считывания файла Excel в tab.
        /// </summary>
        /// <param name="f">Главная форма программы</param>
        public void Inp(Form1 f)
        {
            string str, filename = string.Empty;
            int rowCount;
            int collumnCount;

            this.tab.Clear();

            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Excel (*.XLS;*.XLSX)|*.XLS;*.XLSX";
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                System.IO.StreamReader sr = new
                   System.IO.StreamReader(openFileDialog1.FileName);
                filename = openFileDialog1.FileName;
                sr.Close();
                f.Toggle();
            }
            else
            {
                return;
            }

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook book;
            Microsoft.Office.Interop.Excel.Worksheet sheet;
            Microsoft.Office.Interop.Excel.Range range;

            book = excelApp.Workbooks.Open(filename, 0, true, 5, string.Empty, string.Empty, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.get_Item(1);
            range = sheet.UsedRange;

            for (rowCount = 1; rowCount <= range.Rows.Count; rowCount++)
            {
                this.tab.Add(new List<string>());
                for (collumnCount = 1; collumnCount <= range.Columns.Count; collumnCount++)
                {
                    str = (string)(range.Cells[rowCount, collumnCount] as Microsoft.Office.Interop.Excel.Range).Text;
                    this.tab[rowCount - 1].Add(str.Trim());
                }
            }

            book.Close(true, null, null);
            excelApp.Quit();

            this.ReleaseObject(sheet);
            this.ReleaseObject(book);
            this.ReleaseObject(excelApp);

            int x = 0, y = 0;
            f.Tabl.RowCount = this.tab.Count;
            foreach (List<string> i in this.tab)
            {
                f.Tabl.ColumnCount = i.Count;
                foreach (string u in i)
                {
                    f.Tabl.Rows[x].Cells[y].Value = u;
                    y++;
                }

                y = 0;
                x++;
            }
        }
Пример #58
0
        //法四
        //public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
        //{
        //    DataTable dataTable = dataSet.Tables[0];
        //    int rowNumber = dataTable.Rows.Count;
        //    int columnNumber = dataTable.Columns.Count;
        //    String stringBuffer = "";

        //    if (rowNumber == 0)
        //    {
        //        MessageBox.Show("没有任何数据可以导入到Excel文件!");
        //        return false;
        //    }

        //    //建立Excel对象
        //    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //    excel.Application.Workbooks.Add(true);
        //    excel.Visible = isShowExcle;//是否打开该Excel文件

        //    //填充数据
        //    for (int i = 0; i < rowNumber; i++)
        //    {
        //        for (int j = 0; j < columnNumber; j++)
        //        {
        //            stringBuffer += dataTable.Rows[i].ItemArray[j].ToString();
        //            if (j < columnNumber - 1)
        //            {
        //                stringBuffer += "\t";
        //            }
        //        }
        //        stringBuffer += "\n";
        //    }
        //    Clipboard.Clear();
        //    Clipboard.SetDataObject(stringBuffer);
        //    ((Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1]).Select();
        //    ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Paste(Missing.Value, Missing.Value);
        //    Clipboard.Clear();

        //    return true;
        //}

        //public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
        //{
        //    DataTable dataTable = dataSet.Tables[0];
        //    int rowNumber = dataTable.Rows.Count;
        //    int columnNumber = dataTable.Columns.Count;

        //    if (rowNumber == 0)
        //    {
        //        MessageBox.Show("没有任何数据可以导入到Excel文件!");
        //        return false;
        //    }

        //    //建立Excel对象
        //    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //    Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true);
        //    excel.Visible = false;//是否打开该Excel文件

        //    //填充数据
        //    for (int i = 0; i < rowNumber; i++)
        //    {
        //        for (int j = 0; j < columnNumber; j++)
        //        {
        //            excel.Cells[i + 1, j + 1] = dataTable.Rows[i].ItemArray[j];
        //        }
        //    }

        //    //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
        //    workBook.SaveAs(fileName, 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);

        //    try
        //    {
        //        workBook.Saved = true;
        //        excel.UserControl = false;
        //        //excelapp.Quit();
        //    }
        //    catch (Exception exception)
        //    {
        //        MessageBox.Show(exception.Message);
        //    }
        //    finally
        //    {
        //        workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
        //        excel.Quit();
        //    }

        //    if (isShowExcle)
        //    {
        //        System.Diagnostics.Process.Start(fileName);
        //    }
        //    return true;
        //}

        //public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
        //{
        //    DataTable dataTable = dataSet.Tables[0];
        //    int rowNumber = dataTable.Rows.Count;//不包括字段名
        //    int columnNumber = dataTable.Columns.Count;
        //    int colIndex = 0;

        //    if (rowNumber == 0)
        //    {
        //        MessageBox.Show("没有任何数据可以导入到Excel文件!");
        //        return false;
        //    }

        //    //建立Excel对象
        //    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //    //excel.Application.Workbooks.Add(true);
        //    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        //    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
        //    excel.Visible = isShowExcle;
        //    //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
        //    worksheet.Name = "挠度数据";
        //    Microsoft.Office.Interop.Excel.Range range;

        //    //生成字段名称
        //    foreach (DataColumn col in dataTable.Columns)
        //    {
        //        colIndex++;
        //        excel.Cells[1, colIndex] = col.ColumnName;
        //    }

        //    object[,] objData = new object[rowNumber, columnNumber];

        //    for (int r = 0; r < rowNumber; r++)
        //    {
        //        for (int c = 0; c < columnNumber; c++)
        //        {
        //            objData[r, c] = dataTable.Rows[r][c];
        //        }
        //        //Application.DoEvents();
        //    }

        //    // 写入Excel
        //    range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
        //    //range.NumberFormat = "@";//设置单元格为文本格式
        //    range.Value2 = objData;
        //    worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

        //    //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
        //    workbook.SaveAs(fileName, 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);

        //    try
        //    {
        //        workbook.Saved = true;
        //        excel.UserControl = false;
        //        //excelapp.Quit();
        //    }
        //    catch (Exception exception)
        //    {
        //        MessageBox.Show(exception.Message);
        //    }
        //    finally
        //    {
        //        workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
        //        excel.Quit();
        //    }

        //    //if (isShowExcle)
        //    //{
        //    //    System.Diagnostics.Process.Start(fileName);
        //    //}
        //    return true;
        //}

        /// <summary>
        /// 将数据集中的数据保存到EXCEL文件
        /// </summary>
        /// <param name="dataSet">输入数据集</param>
        /// <param name="fileName">保存EXCEL文件的绝对路径名</param>
        /// <param name="isShowExcle">是否打开EXCEL文件</param>
        /// <returns></returns>
        public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
        {
            DataTable dataTable    = dataSet.Tables[0];
            int       rowNumber    = dataTable.Rows.Count;//不包括字段名
            int       columnNumber = dataTable.Columns.Count;
            int       colIndex     = 0;

            if (rowNumber == 0)
            {
                MessageBox.Show("没有任何数据可以导入到Excel文件!");
                return(false);
            }

            //建立Excel对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            //excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Workbook  workbook  = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            excel.Visible = false;
            //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;

            //生成字段名称
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            object[,] objData = new object[rowNumber, columnNumber];

            for (int r = 0; r < rowNumber; r++)
            {
                for (int c = 0; c < columnNumber; c++)
                {
                    objData[r, c] = dataTable.Rows[r][c];
                }
                //Application.DoEvents();
            }

            // 写入Excel
            range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
            //range.NumberFormat = "@";//设置单元格为文本格式
            range.Value2 = objData;
            worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

            //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
            workbook.SaveAs(fileName, 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);

            try
            {
                workbook.Saved    = true;
                excel.UserControl = false;
                //excelapp.Quit();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {
                workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
                excel.Quit();
            }

            if (isShowExcle)
            {
                System.Diagnostics.Process.Start(fileName);
            }
            return(true);
        }
Пример #59
0
        public void TestCleanToWithExcelApp()
        {
			Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            try
            {
				AppCountMonitor monitor = new AppCountMonitor("excel");
				string testDoc = TestUtils.TestFileUtils.MakeRootPathAbsolute(@"Projects\Workshare.API\Workshare.API.Tests\TestDocs\test.xlsx");
				using (TempFile tf = new TempFile(testDoc))
				{
					using (TempFile dest = new TempFile())
					{
						File.Delete(dest.FilePath);
						string initialHash = tf.MD5Sum;
						IOfficeCleaner c = new OfficeCleaner();
						c.CleanFileTo(tf.FilePath, dest.FilePath, app);
						Assert.IsTrue(File.Exists(dest.FilePath), "We expected the dest file to be created");
						string newHash = dest.MD5Sum;
						Assert.AreNotEqual(initialHash, newHash, "We expected the Cleanion to change the file contents");
					}
				}
				Assert.IsFalse(monitor.SeenMoreInstances(), "Additional instances of Excel were created during the test run - that means it didn't use the provided instance");
            }
            finally
            {
                object oFalse = false;
                app.Quit();
				Marshal.ReleaseComObject(app);
            }
        }
Пример #60
0
        public DataSet ImportExcel(string filePath)
        {
            DataSet   ds = null;
            DataTable dt = null;

            Microsoft.Office.Interop.Excel.Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook    workbook  = null;
            Microsoft.Office.Interop.Excel.Worksheet   worksheet = null;
            Microsoft.Office.Interop.Excel.Sheets      sheets    = null;
            Microsoft.Office.Interop.Excel.Range       range     = null;
            object missing = System.Reflection.Missing.Value;

            try
            {
                if (excel == null)
                {
                    return(null);
                }

                //打开 Excel 文件
                workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

                //获取所有的 sheet 表
                sheets = workbook.Worksheets;

                ds = new DataSet();

                for (int i = 1; i <= sheets.Count; i++)
                {
                    //获取第一个表
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);

                    int rowCount = worksheet.UsedRange.Rows.Count;
                    int colCount = worksheet.UsedRange.Columns.Count;

                    int rowIndex = 1;   //起始行为 1
                    int colIndex = 1;   //起始列为 1

                    DataColumn dc;
                    dt           = new DataTable();
                    dt.TableName = "table" + i.ToString();

                    //读取列名
                    for (int j = 0; j < colCount; j++)
                    {
                        range = worksheet.Cells[rowIndex, colIndex + j];

                        dc            = new DataColumn();
                        dc.DataType   = Type.GetType("System.String");
                        dc.ColumnName = range.Text.ToString().Trim();

                        //添加列
                        dt.Columns.Add(dc);
                    }

                    //读取行数据
                    for (int k = 1; k < rowCount; k++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int l = 0; l < colCount; l++)
                        {
                            range = worksheet.Cells[rowIndex + k, colIndex + l];

                            //使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值
                            dr[l] = range.Text.ToString();
                        }
                        dt.Rows.Add(dr.ItemArray);
                    }

                    ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                workbook.Close();

                //关闭退出
                excel.Quit();

                //释放 COM 对象
                Marshal.ReleaseComObject(worksheet);
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(excel);

                worksheet = null;
                workbook  = null;
                excel     = null;

                GC.Collect();
            }

            return(ds);
        }