private void btn_Excele_Aktar_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i < lb_Dosyalar.Items.Count; i++) { xlWorkSheet.Cells[i + 2, 2] = lb_Dosyalar.Items[i].ToString(); //Dosyaya hyperlink at xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[i + 2, 2], tb_Secilen_Dosya_Yolu.Text + "/" + lb_Dosyalar.Items[i].ToString()); } string kaydedilecek_dosya_yolu = fbr_Dosya_Tarayici_Diyalog.SelectedPath + "/literatur.xls"; xlWorkBook.SaveAs(kaydedilecek_dosya_yolu, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
/// <summary> /// 转换excel为pdf /// </summary> /// <param name="filename">doc文件路径</param> /// <param name="savefilename">pdf保存路径</param> public static void ConvertExcelPDF(string filename, string PDFFileName) { //先引入:Microsoft.Office.Interop //再 using Microsoft.Office.Interop.Excel; Microsoft.Office.Interop.Excel.ApplicationClass cExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); cExcel.Visible = true; object missing = Type.Missing; //excel 文件名: string excelFileName = filename.ToString(); Int32 intLastDot = excelFileName.LastIndexOf("."); string tmp_FilePath = excelFileName.Substring(0, intLastDot); Microsoft.Office.Interop.Excel.Workbook book = null; try { book = cExcel.Workbooks.Open(excelFileName, missing, missing, missing, missing , missing, missing, missing, missing, missing, missing , missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1]; //参数1 var formatType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF; //参数3 var quarlity = Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard; //倒数第二个参数控制是否用pdf软件打开,false会在后台处理,不打开文件 sheet.ExportAsFixedFormat(formatType, PDFFileName, quarlity, true, true, missing, missing, false, missing); } catch (Exception ex) { } finally { if (book != null) { book.Close(missing, missing, missing); book = null; } cExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(cExcel); cExcel = null; GC.Collect(); } }
/// <summary> /// 将excel文档转换成PDF格式 /// </summary> /// <param name="sourcePath">源文件路径</param> /// <param name="targetPath">目标文件路径</param> /// <param name="targetType"></param> /// <returns></returns> public bool ExcelConvertPDF(string sourcePath, string targetPath = null) { if (sourcePath == null) { throw new ArgumentNullException("wpsFilename"); } //保存路径为空时,保存在原始文件目录 if (targetPath == null) { targetPath = Path.ChangeExtension(sourcePath, "pdf"); } bool result; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass application = null; Workbook workBook = null; try { application = new Microsoft.Office.Interop.Excel.ApplicationClass(); object target = targetPath; object type = XlFixedFormatType.xlTypePDF; workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, target, XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch (Exception ex) { result = false; } finally { if (workBook != null) { workBook.Close(true, missing, missing); workBook = null; } if (application != null) { application.Quit(); application = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return(result); }
/// <summary> /// 将EXCEL文档转换成PDF格式 /// </summary> /// <param name="_lstrInputFile"></param> /// <param name="_lstrOutFile"></param> /// <returns></returns> public static bool ConvertExcelToPdf(string _lstrInputFile, string _lstrOutFile) { Excel.XlFixedFormatType parameter = Excel.XlFixedFormatType.xlTypePDF; bool result; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass application = null; Excel.Workbook workBook = null; try { application = new Microsoft.Office.Interop.Excel.ApplicationClass(); object target = _lstrOutFile; object type = parameter; workBook = application.Workbooks.Open(_lstrInputFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workBook.ExportAsFixedFormat(parameter, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch (Exception ex) { result = false; throw new Exception(ex.Message, ex); } finally { if (workBook != null) { workBook.Close(true, missing, missing); workBook = null; } if (application != null) { application.Quit(); application = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return(result); }
/// <summary> /// XLS 2 PDF /// </summary> /// <param name="sourcePath">源路径</param> /// <param name="targetPath">目标路径</param> /// <returns></returns> public bool XLSToPDF(string sourcePath, string targetPath) { bool result = false; Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass application = null; Microsoft.Office.Interop.Excel.Workbook workBook = null; try { application = new Microsoft.Office.Interop.Excel.ApplicationClass(); object target = targetPath; object type = targetType; workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); workBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch { result = false; } finally { if (workBook != null) { workBook.Close(true, missing, missing); workBook = null; } if (application != null) { application.Quit(); application = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return(result); }
public static string ReadExcel(string excelFileName) { string text = string.Empty; Excel.ApplicationClass app = null; Excel.Workbook book = null; object readOnly = true; object missing = System.Reflection.Missing.Value; object fileName = excelFileName; try { app = new Microsoft.Office.Interop.Excel.ApplicationClass(); book = app.Workbooks.Open(fileName.ToString(), missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); foreach (Excel.Worksheet sheet in book.Sheets) { text += "<table>"; for (int i = 1; i <= sheet.UsedRange.Cells.Rows.Count; i++) { text += "<tr><td style=\"padding:6px;border:1px solid #ccc;margin:none;margin:0px;\">" + (i - 1) + "</td>"; for (int j = 1; j <= sheet.UsedRange.Cells.Columns.Count; j++) { text += "<td style=\"padding:6px;border:1px solid #ccc;margin:none;margin:0px;\">" + ((Excel.Range)sheet.Cells[i, j]).Text.ToString().Replace("\r", string.Empty).Replace("\n", string.Empty).Replace("\t", string.Empty) + "</td>"; } text += "</td>"; } text += "</table>"; } } catch { } finally { book.Close(missing, fileName, missing); book = null; app.Quit(); app = null; } return(text); }
private void btnExcel_Click(object sender, EventArgs e) { String temp = "Khach hang WU"; saveFileDialog1.FileName = temp.Replace("/", "-"); saveFileDialog1.Filter = " Excel (*.xls)|*.xls|Tất cả (*.*)|*.*"; saveFileDialog1.FilterIndex = 1; saveFileDialog1.RestoreDirectory = true; string path = ""; if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { Cursor.Current = Cursors.WaitCursor; path = saveFileDialog1.FileName; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 10; //Xuat tieu de cot for (int i = 0; i < dtKHWU.Columns.Count; i++) { ExcelApp.Cells[1, i + 1] = dtKHWU.Columns[i].ColumnName; } //Xuat du lieu for (int j = 0; j < dtKHWU.Rows.Count; j++) { for (int i = 0; i < dtKHWU.Columns.Count; i++) { ExcelApp.Cells[j + 2, i + 1] = dtKHWU.Rows[j][i].ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(path); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); MessageBox.Show("Đã xuất ra file excel."); } Cursor.Current = Cursors.Default; }
private void btnExcel_Click(object sender, EventArgs e) { String temp = "Ke hoach cham soc KH"; saveFileDialog1.FileName = temp.Replace("/", "-"); saveFileDialog1.Filter = " Excel (*.xls)|*.xls|Tất cả (*.*)|*.*"; saveFileDialog1.FilterIndex = 1; saveFileDialog1.RestoreDirectory = true; string path = ""; if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { Cursor.Current = Cursors.WaitCursor; path = saveFileDialog1.FileName; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 10; for (int i = 0; i < dgvDanhsach.Columns.Count; i++) { ExcelApp.Cells[1, i + 1] = dgvDanhsach.Columns[i].Name; } for (int i = 1; i <= dgvDanhsach.Rows.Count; i++) { DataGridViewRow row = dgvDanhsach.Rows[i - 1]; for (int j = 1; j <= row.Cells.Count; j++) { ExcelApp.Cells[i + 1, j] = row.Cells[j - 1].Value.ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs(path); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); MessageBox.Show("Đã xuất ra file excel."); } Cursor.Current = Cursors.Default; }
/////////////////////////////////////////////////////////////// //メソッド名 : FlushGridviewDataToExcel /// <summary> /// 指定GridviewのデータをExcelに出力する /// </summary> /// <param name="targetDataGridView">対象DataGridView</param> /// <history> /// 日付 担当者 内容 /// 2014/06/23 YS.CHEW 新規作成 /// </history> /////////////////////////////////////////////////////////////// public static void FlushGridviewDataToExcel( DataGridView targetDataGridView, string outputFilename ) { Microsoft.Office.Interop.Excel.Application xlApp = null; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null; try { // 保存確認ダイアログの表示 SaveFileDialog dlg = new SaveFileDialog(); dlg.FileName = outputFilename + "_" + Common.GetCurrentTimestamp().ToString("yyyyMMddHHmmss"); dlg.Filter = "Excel files (*.xls)|*.xls"; dlg.FilterIndex = 1; // OKボタンで終了した場合 if (dlg.ShowDialog() == DialogResult.OK) { object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= targetDataGridView.RowCount - 1; i++) { for (j = 0; j <= targetDataGridView.ColumnCount - 1; j++) { // Skip hidden columns if (!targetDataGridView.Columns[j].Visible) continue; DataGridViewCell cell = targetDataGridView[j, i]; if (i == 0) { DataGridViewHeaderCell h = targetDataGridView.Columns[j].HeaderCell; xlWorkSheet.Cells[i + 1, j + 1] = h.Value; } // Code & No columns format if (targetDataGridView.Columns[j].Name.Length > 5 && (targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "CdCol" || targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "NoCol")) { xlWorkSheet.Cells[i + 2, j + 1] = "'" + cell.Value; Excel.Range rng = (Excel.Range)xlWorkSheet.Cells[i + 2, j + 1]; //rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; continue; } // Date columns format if (targetDataGridView.Columns[j].Name.Length > 5 && targetDataGridView.Columns[j].Name.Substring(targetDataGridView.Columns[j].Name.Length - 5) == "DtCol") { xlWorkSheet.Cells[i + 2, j + 1] = cell.Value; Excel.Range rng = (Excel.Range)xlWorkSheet.Cells[i + 2, j + 1]; rng.EntireColumn.NumberFormat = "yyyy/MM/dd"; continue; } xlWorkSheet.Cells[i + 2, j + 1] = cell.Value; } } xlWorkBook.SaveAs(dlg.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } } catch { throw; } finally { releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
private void uploadInfo() { string code = ""; bool isCorrect = false; string errormsg = ""; if (File.Exists(testFile)) { if (File.Exists(sourceFile)) { File.Delete(sourceFile); } File.Copy(testFile, sourceFile); FileInfo fileInfo = new FileInfo(sourceFile); string timeStr = ReadAppSetting("ChangeTime");// ConfigurationManager.AppSettings["ChangeTime"].ToString(); //文件没有更新,不处理 if (timeStr.Length == 0) { SetValue("ChangeTime", fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss")); File.Delete(sourceFile); return; } DateTime dtNow = DateTime.Parse(fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss")); DateTime dtOld = DateTime.Parse(ReadAppSetting("ChangeTime")); //有最新文件,开始上传数据 if (DateTime.Compare(dtNow, dtOld) > 0) { latestTime = dtNow.ToString("yyyy-MM-dd HH:mm:ss"); SetValue("ChangeTime", latestTime); Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //MessageBox.Show(excel.Version); excel.Visible = false; excel.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, "halleluja", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Worksheet ws = (Excel.Worksheet)excel.Worksheets[2]; string name = ((Excel.Range)ws.Cells[3, 3]).Text.ToString(); sourceFileName = name; 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(); if (name.IndexOf("_") > 0) { string[] nameCode = name.Split('_'); if (nameCode.Length == 2) { code = nameCode[1].Trim(); verifyCode = code; try { string strOrder = webService.isExistOrder(code, CurrentUser.UserName); if (strOrder.Length > 2) { vjList = getValidate(strOrder); if (vjList.name.Length > 0) { isCorrect = true; } string pType = vjList.peopleType; //pType = "1"; if (pType == "1") { isAdult = false; } else { isAdult = true; } } else if (strOrder == "-1") { errormsg = "验证码不存在,或者被使用,"; } else if (strOrder == "-2") { errormsg = "门店用户未设置所属门店,"; } else if (strOrder == "-3") { errormsg = "订单预约门店与使用门店不同,"; } else if (strOrder == "-4") { errormsg = "订单状态不可用,"; } } catch (Exception ex) { string er = ex.ToString(); } } else { errormsg = "验证码录入有误,"; } } else { errormsg = "验证码录入有误,"; } } //没有更新文件 else { File.Delete(sourceFile); WriteLog("没有更新文件,数据没有上传!"); return; } //上传文件到服务器 //verifyCode = "hahahaha"; FTPUpLoad(); //有更新文件,但是文件有问题 if (!isCorrect) { File.Delete(sourceFile); //SetValue("ChangeTime", latestTime); WriteLog(sourceFileName + "-" + errormsg + "文件存在问题,数据没有上传!"); MessageBox.Show(sourceFileName + "-" + errormsg + "数据没有上传!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //SetValue("ChangeTime", latestTime); WriteLog("开始上传数据"); //copyTemp(); copyStream(); //上传数据 Thread thdSub = new Thread(new ThreadStart(ThreadFun)); thdSub.Start(); } }
private bool XLSConvertToPDF(string sourcePath, string targetPath) { bool result = false; Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF; object missing = Type.Missing; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null; Microsoft.Office.Interop.Excel._Workbook ExcelBook = null; try { object target = targetPath; object type = targetType; //workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, // missing, missing, missing, missing, missing, missing, missing, missing, missing); // sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx"; System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); System.Reflection.Missing missingValue = System.Reflection.Missing.Value; ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); //ExcelApp.Visible = true; //ExcelApp.ScreenUpdating = true; ////ActiveWindow.SmallScroll Down:=6; //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview; ////ExcelApp.ActiveWindow.SmallScroll = Excel.; //ExcelApp.ActiveWindow.Zoom = 80; ////ActiveWindow.SmallScroll Down:=-3 //// excelRange.WrapText = true; ////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 //ActiveWindow.SmallScroll Down:=30 //Set ActiveSheet.HPageBreaks(1).Location = Range("A67") //ActiveWindow.SmallScroll Down:=-75 //Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2]; ////上边距 //double top = 0; ////左边距 //double left = 0; ////右边距 //double right = 0; ////下边距 //double footer = 0; //WS2.DisplayAutomaticPageBreaks = false;//显示分页线 //WS2.PageSetup.CenterFooter = "第 &P 页,共 &N 页"; //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上 //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下 //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左 //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右 //WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行 //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向 //Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]); //自动调整列宽 //// excelRange.EntireColumn.AutoFit(); //// excelRange.WrapText = false; //文本自动换行 //excelRange.ShrinkToFit = false; ////设置字体在单元格内的对其方式 //excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter; //// 文本水平居中方式 //excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //////设置为横向打印 ////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; ////ExcelApp.ActiveWindow.FreezePanes = true; ////excelRange.EntireColumn.AutoFit(); ////WS2.PageSetup.Orientation = 2; //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0); //WS2.PageSetup.CenterHorizontally = true; //// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]); //WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1); ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch { result = false; } finally { if (ExcelBook != null) { ExcelBook.Close(true, missing, missing); ExcelBook = null; } if (ExcelApp != null) { ExcelApp.Quit(); ExcelApp = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return(result); }
private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result) { try { ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString()); #region 获取模板路径 string fullPath = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls"; SaveFileDialog sfdDownFile = new SaveFileDialog(); sfdDownFile.OverwritePrompt = false; string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)); sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx"; string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\"); sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls"); string strExcelFileName = string.Empty; string ResaveName = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls"; #endregion #region 导出前校验模板信息 if (string.IsNullOrEmpty(sfdDownFile.FileName)) { MessageBox.Show("File name can't be empty, please Check, thanks!"); return(""); } if (!File.Exists(fullPath)) { MessageBox.Show("Template file does not exist, please Check, thanks!"); return(""); } else { strExcelFileName = sfdDownFile.FileName; } #endregion #region Excel 初始化 System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Range rng; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); System.Reflection.Missing missingValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel._Workbook ExcelBook = ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue); #endregion ProcessLogger.Fatal("8949 Input Initial" + DateTime.Now.ToString()); #region 导入 try { #region Sheet 初始化 Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1]; #region 填充数据 int RowIndex = 6; string fapiaoleixingin = ""; string guidangren = ""; string danghao = ""; string guidangshijian = ""; foreach (clsFAinfo item in Result) { fapiaoleixingin = item.fapiaoleixing; guidangren = item.guidangrenzhanghao; danghao = item.danganhao; if (item.Input_Date != null && item.Input_Date.Length > 8) { guidangshijian = item.Input_Date.Substring(0, 8); } RowIndex++; ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao; ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao; //ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao; } //ExcelApp.Visible = true; //ExcelApp.ScreenUpdating = true; ExcelSheet.Cells[1, 1] = "总件数:" + Result.Count.ToString(); ExcelSheet.Cells[2, 1] = "发票类型:" + fapiaoleixingin; ExcelSheet.Cells[3, 1] = "归档人:" + guidangren; ExcelSheet.Cells[4, 1] = "档号:" + danghao; ExcelSheet.Cells[5, 1] = "归档时间:" + guidangshijian; //直接打印 //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview; //ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4; //ExcelBook.PrintOut(); #region 写入文件 ProcessLogger.Fatal("8950 Output Initial" + DateTime.Now.ToString()); ExcelApp.DisplayAlerts = false; ExcelApp.ScreenUpdating = true; ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue); ExcelApp.DisplayAlerts = false; ExcelBook.Close(false, missingValue, missingValue); ExcelBook = null; #endregion //转换成PDF 文件 //if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf"))) //{ // // FilePath.Add(ResaveName.Replace("xlsx", "pdf")); //} return(ResaveName); #endregion #endregion } #endregion #region 异常处理 catch (Exception ex) { ExcelApp.DisplayAlerts = false; ExcelApp.Quit(); ExcelBook = null; ExcelApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); throw ex; } #endregion #region Finally垃圾回收 finally { //ExcelBook.Close(false, missingValue, missingValue); //ExcelBook = null; ExcelApp.DisplayAlerts = true; ExcelApp.Quit(); clsKeyMyExcelProcess.Kill(ExcelApp); GC.Collect(); GC.WaitForPendingFinalizers(); } #endregion } catch (Exception ex) { throw ex; } }
protected void btnFileRead_Click(object sender, EventArgs e) { try { string thisDir = Server.MapPath("~"); string epath = thisDir + "\\MarketingExcel\\" + Convert.ToString(Session["MarketingUser"]) + "\\"; string Name = epath + Convert.ToString(Session["MarketingUser"]); Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; Microsoft.Office.Interop.Excel.Range range; DataTable dt = new DataTable(); dt.Columns.Add("fName"); dt.Columns.Add("lName"); dt.Columns.Add("mobileNo"); dt.Columns.Add("pinNo"); DataTable dt1 = new DataTable(); dt1.Columns.Add("fName"); dt1.Columns.Add("lName"); dt1.Columns.Add("mobileNo"); dt1.Columns.Add("pinNo"); xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(Name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; int index = 0; object rowIndex = 2; while (((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 1]).Value2 != null) { urUserRegBLLObj.usrFirstName = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 2]).Value2); urUserRegBLLObj.usrLastName = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 3]).Value2); urUserRegBLLObj.usrMobileNo = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 4]).Value2); urUserRegBLLObj.usrPIN = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 5]).Value2); urUserRegBLLObj.usrAddress = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[rowIndex, 6]).Value2); urUserRegBLLObj.usrCityId = Convert.ToInt32(Session["CityIdN"]); urUserRegBLLObj.frnrelGroup = "1"; string mobNo = urUserRegBLLObj.usrMobileNo; int count = Convert.ToInt32(mobNo.Length); if (urUserRegBLLObj.usrFirstName != "") { if (urUserRegBLLObj.usrLastName != "") { if (count == 10) { status = urUserRegBLLObj.BLLIsExistUserRegistrationInitial(urUserRegBLLObj); if (status > 0) { urUserRegBLLObj.usrUserId = System.Guid.NewGuid().ToString(); Random rnd = new Random(); urUserRegBLLObj.usrPassword = cc.DESEncrypt(Convert.ToString(rnd.Next(10001, 99999))); status = urUserRegBLLObj.BLLInsertUserRegistrationInitial(urUserRegBLLObj); if (status > 0) { DataRow drN; drN = dt.NewRow(); string fName = Convert.ToString(urUserRegBLLObj.usrFirstName); string lName = Convert.ToString(urUserRegBLLObj.usrLastName); string mobileNo = Convert.ToString(urUserRegBLLObj.usrMobileNo); string pinNo = Convert.ToString(urUserRegBLLObj.usrPIN); drN[0] = fName; drN[1] = lName; drN[2] = mobileNo; drN[3] = pinNo; dt.Rows.Add(drN); gvUserRegistered.DataSource = dt; gvUserRegistered.DataBind(); urUserRegBLLObj.frnrelUserId = Convert.ToString(Session["User"]); urUserRegBLLObj.frnrelFriendId = urUserRegBLLObj.usrUserId; urUserRegBLLObj.frnrelFrnRelName = Convert.ToString(urUserRegBLLObj.usrFirstName + " " + urUserRegBLLObj.usrLastName); int frcount = urUserRegBLLObj.BLLInsertUserFriendRelative(urUserRegBLLObj); if (frcount > 0) { usrFrndRelSms(urUserRegBLLObj.usrMobileNo); } } } else { DataRow drN; drN = dt1.NewRow(); string fName = Convert.ToString(urUserRegBLLObj.usrFirstName); string lName = Convert.ToString(urUserRegBLLObj.usrLastName); string mobileNo = Convert.ToString(urUserRegBLLObj.usrMobileNo); string pinNo = Convert.ToString(urUserRegBLLObj.usrPIN); drN[0] = fName; drN[1] = lName; drN[2] = mobileNo; drN[3] = pinNo; dt1.Rows.Add(drN); gvUserAlreadyRegistered.DataSource = dt1; gvUserAlreadyRegistered.DataBind(); } } else { ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Mobile No should Be 10 Digits for Mob NO.:" + mobNo + "')", true); } } else { ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the Last Name of Mobile No :" + mobNo + "')", true); } } else { ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the First Name of Mobile No :" + mobNo + "')", true); } index++; rowIndex = 2 + index; } xlWorkBook.Close(true, null, null); xlApp.Quit(); } catch (Exception ex) { throw ex; //string msg = ex.Message; //Response.Write(ex.Message); //ScriptManager.RegisterStartupScript(this, typeof(Page), "msg", "alert('Please Eneter the First Name of Mobile No ')", true); } }