private void tsbtn_Query_Click(object sender, EventArgs e) { CloseProcess("EXCEL"); //关闭所有Excel进程 string P_str_Excel = tstxt_Excel.Text; //记录Excel文件路径 string P_str_SheetName = tscbox_Sheet.Text; //记录选择的工作表名称 object P_obj_Start = tstxt_Start.Text; //记录开始单元格 object P_obj_End = tstxt_End.Text; //记录结束单元格 object missing = System.Reflection.Missing.Value; //定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]); //获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range(P_obj_Start, P_obj_End); //定义查找范围 Microsoft.Office.Interop.Excel.Range currentRange = null; //定义当前找到的范围 Microsoft.Office.Interop.Excel.Range firstRange = null; //定义找到的第一个范围 object P_obj_Text = tstxt_Text.Text; //记录要搜索的文本 //搜索第一个匹配项,指定从其后开始搜索的单元格以外的所有参数 currentRange = searchRange.Find(P_obj_Text, missing, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing); //一直搜索,直到没有匹配项 while (currentRange != null) { if (firstRange == null) //如果第一个范围不包含任何值 { firstRange = currentRange; //记录当前范围 } //如果查找范围的地址与第一个查找范围的地址匹配 else if (currentRange.get_Address(missing, missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing) == firstRange.get_Address(missing, missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing)) { break;//退出 } //为单元格加边框 currentRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Color.Black.ToArgb()); currentRange.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Red); //设置搜索到的文本颜色 currentRange.Font.Bold = true; //设置搜索到的文本为粗体 currentRange = searchRange.FindNext(currentRange); //查找下一处 } MessageBox.Show("搜索完毕!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); excel.DisplayAlerts = false; //设置保存Excel时不显示对话框 workbook.Save(); //保存工作表 CloseProcess("EXCEL"); //关闭所有Excel进程 WBrowser_Excel.Navigate(P_str_Excel); //在窗体中重新显示Excel文件内容 }
/// <summary> /// 导出数据到Excel中 /// </summary> /// <param name="dt">DataTable数据源</param> /// <returns></returns> public void ExportToExcel(System.Data.DataTable dt) { if (dt == null) { return; } Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog(); saveDia.Filter = "Excel|*.xls"; saveDia.Title = "导出为Excel文件"; if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK && !string.Empty.Equals(saveDia.FileName)) { 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 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; string fileName = saveDia.FileName; //写入标题 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; //range.Interior.ColorIndex = 15;//背景颜色 range.Font.Bold = true; //粗体 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous); //range.ColumnWidth = 4.63;//设置列宽 //range.EntireColumn.AutoFit();//自动调整列宽 //r1.EntireRow.AutoFit();//自动调整行高 } //写入内容 for (int r = 0; r < dt.DefaultView.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i]; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1]; range.Font.Size = 9;//字体大小 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous); range.EntireColumn.AutoFit();//自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 Process[] excelProc = Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killId = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killId = m; } } if (excelProc[killId].HasExited == false) { excelProc[killId].Kill(); } #endregion MessageBox.Show("导出成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
//输出文件到xlsx /// <summary> /// 导出文件。该方法使用的数据源为DataTable,导出Excel文件。 /// </summary> /// <param name="dt"></param> public static void ExportToExcel(DataTable dt, string path) { Microsoft.Office.Interop.Excel.Application xlxsApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = xlxsApp.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 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; string fileName = path; //写入标题 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; //range.Interior.ColorIndex = 15;//背景颜色 range.Font.Bold = true; //粗体 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //range.ColumnWidth = 4.63;//设置列宽 //range.EntireColumn.AutoFit();//自动调整列宽 //r1.EntireRow.AutoFit();//自动调整行高 } //写入内容 for (int r = 0; r < dt.DefaultView.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i]; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1]; range.Font.Size = 9; //字体大小 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); range.EntireColumn.AutoFit(); //自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); //进度条 exportThread(r); } //满格显示 exportThread(dt.DefaultView.Count); range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); return; } workbooks.Close(); if (xlxsApp != null) { xlxsApp.Workbooks.Close(); xlxsApp.Quit(); int generation = System.GC.GetGeneration(xlxsApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlxsApp); xlxsApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killId = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killId = m; } } if (excelProc[killId].HasExited == false) { excelProc[killId].Kill(); } #endregion MessageBox.Show("导出成功!"); }
/// <summary> /// Extension method to write list data to Microsoft.Office.Interop.Excel File. /// </summary> /// <typeparam name="T">Ganeric list</typeparam> /// <param name="list"></param> /// <param name="PathToSave">Path to save file.</param> public static void ToExcel <T>(this List <T> list, string PathToSave) { #region Declarations if (string.IsNullOrEmpty(PathToSave)) { throw new Exception("Invalid file path."); } else if (PathToSave.ToLower().Contains("") == false) { throw new Exception("Invalid file path."); } if (list == null) { throw new Exception("No data to export."); } Microsoft.Office.Interop.Excel.Application excelApp = null; Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel._Workbook book = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel._Worksheet sheet = null; Microsoft.Office.Interop.Excel.Range range = null; Microsoft.Office.Interop.Excel.Font font = null; // Optional argument variable object optionalValue = Missing.Value; string strHeaderStart = "A2"; string strDataStart = "A3"; #endregion #region Processing try { #region Init Microsoft.Office.Interop.Excel app. excelApp = new Microsoft.Office.Interop.Excel.Application(); books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks; book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue)); sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets; sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1)); #endregion #region Creating Header Dictionary <string, string> objHeaders = new Dictionary <string, string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName); } range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(1, objHeaders.Count); range.set_Value(optionalValue, objHeaders.Values.ToArray()); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); font = range.Font; font.Bold = true; range.Interior.Color = Color.LightGray.ToArgb(); #endregion #region Writing data to cell int count = list.Count; object[,] objData = new object[count, objHeaders.Count]; for (int j = 0; j < count; j++) { var item = list[j]; int i = 0; foreach (KeyValuePair <string, string> entry in objHeaders) { var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null); objData[j, i++] = (y == null) ? "" : y.ToString(); } } range = sheet.get_Range(strDataStart, optionalValue); range = range.get_Resize(count, objHeaders.Count); range.set_Value(optionalValue, objData); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(count + 1, objHeaders.Count); range.Columns.AutoFit(); #endregion #region Saving data and Opening Microsoft.Office.Interop.Excel file. if (string.IsNullOrEmpty(PathToSave) == false) { book.SaveCopyAs(PathToSave); } excelApp.Visible = true; #endregion #region Release objects try { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } sheet = null; if (sheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); } sheets = null; if (book != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); } book = null; if (books != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(books); } books = null; if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } excelApp = null; } catch (Exception ex) { sheet = null; sheets = null; book = null; books = null; excelApp = null; } finally { GC.Collect(); } #endregion } catch (Exception ex) { throw ex; } #endregion }
void CreateExcelDocument() { System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { string FirstVenue = exitems[CB_FirstOffer.SelectedIndex].Venue + "_" + exitems[CB_FirstOffer.SelectedIndex].Address; string SecondVenue = exitems[CB_SecondOffer.SelectedIndex].Venue + "_" + exitems[CB_SecondOffer.SelectedIndex].Address; xlWorkSheet.Name = FirstVenue.Substring(0, 14) + " - " + SecondVenue.Substring(0, 14); xlWorkSheet.Cells[1, 1] = FirstVenue; xlWorkSheet.Cells[1, 4] = SecondVenue; Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1", "D1"); formatRange.Font.Bold = true; formatRange.WrapText = true; xlWorkSheet.Range["a1", "b1"].Merge(); xlWorkSheet.Range["d1", "e1"].Merge(); string rang = "A1:" + "E" + (SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2).ToString() : (FirstComp.Count + 2).ToString()); xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS"; xlWorkSheet.Range[rang].Font.Size = 16; xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink); xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; for (int i = 0; i < FirstComp.Count; i++) { xlWorkSheet.Cells[2 + i, 1] = FirstComp[i].ExpenseName; xlWorkSheet.Cells[2 + i, 2] = FirstComp[i].Expense; xlWorkSheet.Cells[2 + i, 2].NumberFormat = "0"; } for (int i = 0; i < SecondComp.Count; i++) { xlWorkSheet.Cells[2 + i, 4] = SecondComp[i].ExpenseName; xlWorkSheet.Cells[2 + i, 5] = SecondComp[i].Expense; xlWorkSheet.Cells[2 + i, 5].NumberFormat = "0"; } xlWorkSheet.Cells[(SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2) : (FirstComp.Count + 2)), 1] = LB_FirstAmount.Content.ToString().Split(' ')[0]; xlWorkSheet.Cells[(SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2) : (FirstComp.Count + 2)), 2] = LB_FirstAmount.Content.ToString().Split(' ')[1]; xlWorkSheet.Cells[(SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2) : (FirstComp.Count + 2)), 4] = LB_SecondAmount.Content.ToString().Split(' ')[0]; xlWorkSheet.Cells[(SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2) : (FirstComp.Count + 2)), 5] = LB_SecondAmount.Content.ToString().Split(' ')[1]; xlWorkSheet.get_Range(rang).Columns.AutoFit(); rang = "A" + (SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2).ToString() : (FirstComp.Count + 2).ToString()) + ":" + "E" + (SecondComp.Count >= FirstComp.Count ? (SecondComp.Count + 2).ToString() : (FirstComp.Count + 2).ToString()); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; xlApp.DisplayAlerts = false; Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog(); int fvenue = FirstVenue.Length; int svenue = SecondVenue.Length; saveFileDialog.FileName = (rm as ResourceManager).GetString("Menu_Comparsion") + "_" + FirstVenue.Substring(0, fvenue < 15?fvenue:15) + "_" + SecondVenue.Substring(0, svenue < 15?svenue:15); saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter"); saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle"); if (saveFileDialog.ShowDialog() == true) { System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName); if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false) { xlWorkBook.SaveAs(saveFileDialog.FileName); ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle); Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false); wmsg.Show(); } } xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } catch (Exception ex) { System.Windows.Input.Mouse.OverrideCursor = null; ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error); Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false); msb.Show(); xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } System.Windows.Input.Mouse.OverrideCursor = null; }
void CreateExcelDocument() { System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { xlWorkSheet.Name = LB_Guests.Content.ToString(); xlWorkSheet.Cells[1, 1] = LB_Bride.Content; xlWorkSheet.Cells[1, 4] = LB_Groom.Content; Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1", "D1"); formatRange.Font.Bold = true; formatRange.WrapText = true; xlWorkSheet.Range["a1", "b1"].Merge(); xlWorkSheet.Range["d1", "e1"].Merge(); List <Models.Guests> GroomGuestList = guestList.Where(x => x.Bride_Groom.Equals(0)).ToList(); List <Models.Guests> BrideGuestList = guestList.Where(x => x.Bride_Groom.Equals(1)).ToList(); string rang = "A1:" + "E" + (BrideGuestList.Count >= GroomGuestList.Count ? (BrideGuestList.Count + 1).ToString() : (GroomGuestList.Count + 1).ToString()); xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS"; xlWorkSheet.Range[rang].Font.Size = 16; xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink); xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; for (int i = 0; i < BrideGuestList.Count; i++) { xlWorkSheet.Cells[2 + i, 1] = BrideGuestList[i].Guest_Name.Trim(); xlWorkSheet.Cells[2 + i, 2] = BrideGuestList[i].Guest_Count; xlWorkSheet.Cells[2 + i, 2].NumberFormat = "0"; } for (int i = 0; i < GroomGuestList.Count; i++) { xlWorkSheet.Cells[2 + i, 4] = GroomGuestList[i].Guest_Name.Trim(); xlWorkSheet.Cells[2 + i, 5] = GroomGuestList[i].Guest_Count; xlWorkSheet.Cells[2 + i, 5].NumberFormat = "0"; } xlWorkSheet.get_Range(rang).Columns.AutoFit(); xlApp.DisplayAlerts = false; Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog(); saveFileDialog.FileName = (rm as ResourceManager).GetString("Menu_Guests"); saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter"); saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle"); if (saveFileDialog.ShowDialog() == true) { System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName); if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false) { xlWorkBook.SaveAs(saveFileDialog.FileName); ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle); Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false); wmsg.Show(); } } xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } catch (Exception ex) { System.Windows.Input.Mouse.OverrideCursor = null; ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error); Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false); msb.Show(); xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } System.Windows.Input.Mouse.OverrideCursor = null; }
private void ExcelFeedBack_User(List <string> idimage) { if (File.Exists(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx")) { File.Delete(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx"); File.WriteAllBytes((Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User); } else { File.WriteAllBytes( (Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "/Feedback_User.xlsx"), Properties.Resources.Feedback_User); } int r = 1; //int distance = 15; App = new Microsoft.Office.Interop.Excel.Application(); book = App.Workbooks.Open(System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments) + "\\Feedback_User.xlsx", 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; string pathServer = Global.Webservice + Folder + "/" + cbb_batch.Text; for (int i = 0; i < idimage.Count; i++) { string id = idimage[i]; Microsoft.Office.Interop.Excel.Range oRange = wrksheet.Cells[r + 1, 2]; float Left = (float)((double)oRange.Left); float Top = (float)((double)oRange.Top + 2); Microsoft.Office.Interop.Excel.Range final = wrksheet.Cells[r + 20, 9]; float leftFinal = (float)((double)final.Left) - Left - 1; float topFinal = (float)((double)final.Top + 2) - Top; wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, leftFinal, topFinal);//365, 270); // oRange.RowHeight = 367; // wrksheet.Cells[1, 20] = wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270); //Image oImage = Image.FromFile(pathServer + "/" + idimage[i]); //wrksheet.Shapes.AddPicture(pathServer + "/" + idimage[i], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 20, distance, 365, 270); // distance += 300; var deso = Global.Db.FeedBackExcel_User(id, cbb_username.Text, cbb_batch.Text).ToList(); int h = 9; wrksheet.Cells[r + 1, h] = "UserName"; wrksheet.Cells[r + 2, h] = "Trường 1"; wrksheet.Cells[r + 3, h] = "Trường 3"; wrksheet.Cells[r + 4, h] = "Trường 4"; wrksheet.Cells[r + 5, h] = "Trường 5"; wrksheet.Cells[r + 6, h] = "Trường 6"; wrksheet.Cells[r + 7, h] = "Trường 7"; wrksheet.Cells[r + 8, h] = "Trường 8.1"; wrksheet.Cells[r + 9, h] = "Trường 8.2"; wrksheet.Cells[r + 10, h] = "Trường 9"; wrksheet.Cells[r + 11, h] = "Trường 10"; wrksheet.Cells[r + 12, h] = "Trường 11"; wrksheet.Cells[r + 13, h] = "Trường 12"; wrksheet.Cells[r + 14, h] = "Trường 13"; wrksheet.Cells[r + 15, h] = "Trường 14"; // wrksheet.Cells[r + 15, h] = "FlagError"; wrksheet.Cells[r + 20, 1] = id + ""; wrksheet.Cells[r + 1, 1] = i + 1; for (int j = 0; j < deso.Count(); j++) { h++; wrksheet.Cells[r + 1, h] = deso[j].UserName + ""; wrksheet.Cells[r + 2, h] = deso[j].TruongSo01 + ""; wrksheet.Cells[r + 3, h] = deso[j].TruongSo03 + ""; wrksheet.Cells[r + 4, h] = deso[j].TruongSo04 + ""; wrksheet.Cells[r + 5, h] = deso[j].TruongSo05 + ""; wrksheet.Cells[r + 6, h] = deso[j].TruongSo06 + ""; wrksheet.Cells[r + 7, h] = deso[j].TruongSo07 + ""; wrksheet.Cells[r + 8, h] = deso[j].TruongSo08 + ""; wrksheet.Cells[r + 9, h] = deso[j].TruongSo08_2 + ""; wrksheet.Cells[r + 10, h] = deso[j].TruongSo09 + ""; wrksheet.Cells[r + 11, h] = deso[j].TruongSo10 + ""; wrksheet.Cells[r + 12, h] = deso[j].TruongSo11 + ""; wrksheet.Cells[r + 13, h] = deso[j].TruongSo12 + ""; wrksheet.Cells[r + 14, h] = deso[j].TruongSo13 + ""; wrksheet.Cells[r + 15, h] = deso[j].TruongSo14 + ""; // wrksheet.Cells[r + 15, h] = deso[j].FlagError + ""; } Microsoft.Office.Interop.Excel.Range cellImage1 = wrksheet.Cells[h - 10][r + 1]; Microsoft.Office.Interop.Excel.Range cellImage2 = wrksheet.Cells[h][r + 20]; Microsoft.Office.Interop.Excel.Range rangeImage = wrksheet.get_Range(cellImage1, cellImage2); rangeImage.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, 1); Microsoft.Office.Interop.Excel.Range cell1 = wrksheet.Cells[h - 2][r + 1]; Microsoft.Office.Interop.Excel.Range cell2 = wrksheet.Cells[h][r + 15]; Microsoft.Office.Interop.Excel.Range range = wrksheet.get_Range(cell1, cell2); range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); r += 21; } string savePath = ""; saveFileDialog1.Title = "Save Excel Files"; saveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx"; saveFileDialog1.FileName = "Feedback_" + cbb_batch.Text + "_" + cbb_username.Text; 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; } Process.Start(savePath); }
public void ExportToExcel(DataTable dt, string filePath) { if (dt == null)//检查数据表是否为空,如果为空,则退出 { return; } //创建Excel应用程序对象,如果未创建成功则退出 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 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; for (int i = 0; i < dt.Columns.Count; i++) //写入标题 { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; //写入标题名称 //设置标题的样式 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Font.Bold = true; //粗体 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色 range.EntireColumn.AutoFit(); //自动设置列宽 range.EntireRow.AutoFit(); //自动设置行高 } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count; c++) { //写入内容 worksheet.Cells[r + 2, c + 1] = "'" + dt.Rows[r][c].ToString(); //设置样式 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, c + 1]; range.Font.Size = 9; //字体大小 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 range.EntireColumn.AutoFit(); //自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(filePath); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); return; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect(); //强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion }
private void btnBackUP_Click(object sender, EventArgs e) { if (this.cbxSelectKQuan.SelectedItem == null) { MessageBox.Show("请选择矿权!"); return; } if (this.cbxUpdateKQuan.SelectedItem == null || this.cbxKQu.SelectedItem == null || this.dateTimeInput1 == null || this.txtUpdateReason == null || this.txtOperator == null || this.txtManager == null || this.txtComment == null) { MessageBox.Show("请完善记录信息!"); return; } string[] TableName = { "JGAB301_核查矿区", "JGAB302_原上表矿区", "JGAB303_勘查工作区", "JGAB304_采矿权", "JGAB305_探矿权", "JGAB306_矿体", "JGAB307_采空区", "JGAB308_核查块段", "JGAB309_核查块段储量", "JGAB310_原块段", "JGAB311_原块段储量", "JGAB312_块段对照表", "JGAB313_资料目录", "JGAB314_附件目录", "JGAB315_专题图件", "JGAB316_专题图件图层", "JGAB317_煤质特征", "JGAB318_储量利用", "JGAB319_大块段对照表", "JGAB320_合并原块段", "JGAB321_采矿权三率" }; string[] tableField = { "TZYSBH", "TYBH", "HCKQBH", "CKZBH", "CKQR", "CKQFW", "DZ", "KSBH", "KSMC", "FZJG", "YXQQ", "YXQZ", "XKCSS", "XKCSX", "KCZKZ", "ZKZMC", "ZYJSL", "ZYKSL", "BYJSL", "BYKSL", "DKSNL", "DJSNL", "SKSNL", "SJSNL", "NDKSL", "NDJSL", "KCFSM", "KCFS", "XKFSM", "XKFS", "RXKSL", "KQBH", "JJLXM", "JJLX", "CYRY", "NCZ", "JSSCCB", "KCPLX", "CXSBSL" }; // 文件保存路径及名称 // 创建Excel文档 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet sheet = null; string sheetName = ""; //删除自己生成的两个sheet for (int i = 2; i < 4; i++) { sheetName = "Sheet" + i; sheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Sheets.get_Item(sheetName); sheet.Delete(); } for (int i = 0; i < 1; i++) { ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Name = "更新日志表"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 1] = "更新矿权";//也可以这样赋值 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 2] = "所属矿区"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 3] = "更新时间"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 4] = "更新原因"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 5] = "负责人"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 6] = "操作员"; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[2, 7] = "备注"; //合并 单元格 设置表头 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F2").MergeCells); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").Merge(((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G2").MergeCells); //得到 Range 范围 域对象 Microsoft.Office.Interop.Excel.Range range = ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G69"); //设置 该range内的 样式 颜色 边框 ////设置Excel表格的 列宽 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "A69").ColumnWidth = 20; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("B1", "B69").ColumnWidth = 20; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("C1", "C69").ColumnWidth = 30; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("D1", "D69").ColumnWidth = 20; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("E1", "E69").ColumnWidth = 20; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("F1", "F69").ColumnWidth = 30; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("G1", "G69").ColumnWidth = 30; //设置 域 Range 的颜色 从 A1到W1 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G1").Interior.ColorIndex = 15; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A2", "G2").Interior.ColorIndex = 15; //设置某个域range被选中 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G3").Select(); //左右 设置 选中域内的 Excel单元格从C 到W 是活动的 前面的A B 为固定的 //但是 上下 方向 表头(这里表头合并两行 )没有固定 选C3 到W3(表示从C的第三行开始 为 活动 的 上面两行为固定的) ExcelApp.ActiveWindow.FreezePanes = true; //设置 某个域range内 单元格里的字体颜色 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A1", "G2").Font.Color = -16744448;//(搜索Excel颜色对照表) ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).get_Range("A3", "G24").Font.Color = -16776961; //文字 居中 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.Font.Size = 10; range.Borders.LineStyle = 1; //设置边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium; range.WrapText = true; //赋值 就 for (int j = 0; j < 1; j++) { ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 1] = this.cbxSelectKQuan.SelectedItem.ToString(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 2] = this.cbxKQu.SelectedItem.ToString(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 3] = this.dateTimeInput1.Text.ToString(); ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 4] = this.txtUpdateReason.Text; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 5] = this.txtManager.Text; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 6] = this.txtOperator.Text; ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[i + 1]).Cells[j + 3, 7] = this.txtComment.Text; } } ExcelApp.DisplayAlerts = true; object missing = System.Reflection.Missing.Value; // 文件保存 string excelPath = historyPath + "\\矿权-" + this.cbxSelectKQuan.SelectedItem.ToString() + "-" + DateTime.Today.Year.ToString() + "年" + DateTime.Today.Month.ToString() + "月.xls"; ExcelBook.SaveAs(excelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); ExcelBook.Close(Type.Missing, excelPath, Type.Missing); ExcelApp.Quit(); string P_str_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + access_Path + ";Persist Security Info=False"; OleDbConnection oledbcon = new OleDbConnection(P_str_Con);//实例化OLEDB连接对象 //使用事务保持数据的一致性与完整性 string P_str_Sql_04 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB304_采矿权] from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句 string P_str_Sql_21 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB321_采矿权三率] from JGAB321_采矿权三率 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句 string P_str_Sql_08 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB308_核查块段] from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'"; //记录连接Excel的语句 string P_str_Sql_06 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB306_矿体] from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_09 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB309_核查块段储量] from JGAB309_核查块段储量 where (TYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_12 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB312_块段对照表] from JGAB312_块段对照表 where (HCTYBH IN (select DISTINCT TYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_10 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB310_原块段] from JGAB310_原块段 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_11 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB311_原块段储量] from JGAB311_原块段储量 where (KDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_18 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB318_储量利用] from JGAB318_储量利用 where (TYBH IN (select DISTINCT CLLYTYBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_17 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB317_煤质特征] from JGAB317_煤质特征 where (TYBH IN (select DISTINCT MCBH from JGAB306_矿体 where (KTBH IN (select DISTINCT KTBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))"; string P_str_Sql_19 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB319_大块段对照表] from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_20 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB320_合并原块段] from JGAB320_合并原块段 where (HBTYBH IN (select DISTINCT YTYBH from JGAB319_大块段对照表 where (YKDBH IN (select DISTINCT YKDBH from JGAB308_核查块段 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))))"; string P_str_Sql_01 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB301_核查矿区] from JGAB301_核查矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_02 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB302_原上表矿区] from JGAB302_原上表矿区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_03 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB303_勘查工作区] from JGAB303_勘查工作区 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_05 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB305_探矿权] from JGAB305_探矿权 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_07 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB307_采空区] from JGAB307_采空区 where CKQBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'"; string P_str_Sql_13 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB313_资料目录] from JGAB313_资料目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_14 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB314_附件目录] from JGAB314_附件目录 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_15 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB315_专题图件] from JGAB315_专题图件 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string P_str_Sql_16 = "select * into [Excel 8.0;database=" + excelPath + "]." + "[JGAB316_专题图件图层] from JGAB316_专题图件图层 where (HCKQBH IN (select DISTINCT HCKQBH from JGAB304_采矿权 where CKZBH = '" + this.cbxSelectKQuan.SelectedItem.ToString() + "'))"; string[] SQLList = { P_str_Sql_04, P_str_Sql_21, P_str_Sql_08, P_str_Sql_06, P_str_Sql_09, P_str_Sql_12, P_str_Sql_10, P_str_Sql_11 , P_str_Sql_18, P_str_Sql_17, P_str_Sql_19, P_str_Sql_20, P_str_Sql_01, P_str_Sql_02, P_str_Sql_03,P_str_Sql_05, P_str_Sql_07, P_str_Sql_13, P_str_Sql_14 , P_str_Sql_15, P_str_Sql_16 }; oledbcon.Open();//打开数据库连接 OleDbCommand oledbcom = new OleDbCommand(); oledbcom.Connection = oledbcon; oledbcom.Transaction = oledbcon.BeginTransaction();//开始事务 try { for (int i = 0; i < SQLList.Length; i++) { string strsql = SQLList[i].ToString(); if (strsql.Trim().Length > 1) { oledbcom.CommandText = strsql; oledbcom.ExecuteNonQuery(); } //SetTextMessage(i * 100 / SQLList.Length); } oledbcom.Transaction.Commit(); MessageBox.Show("操作成功!"); } catch (Exception ex) { MessageBox.Show("操作遇到问题,已撤销所做操作!"); oledbcom.Transaction.Rollback();//回滚数据,保证数据的完整性 } finally { oledbcon.Close(); //关闭数据库连接 oledbcon.Dispose(); //释放资源 } }
void CreateExcelDocument() { System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlWorkBooks.Add(1); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); try { xlWorkSheet.Name = (rm as ResourceManager).GetString("LB_Expenses"); xlWorkSheet.Cells[1, 1] = exitems[CB_Offer.SelectedIndex].Venue.Trim() + " - " + exitems[CB_Offer.SelectedIndex].Address.Trim(); Microsoft.Office.Interop.Excel.Range formatRange = xlWorkSheet.get_Range("A1", "D1"); formatRange.Font.Bold = true; formatRange.WrapText = true; xlWorkSheet.Range["a1", "D1"].Merge(); string rang = "A1:" + "D" + (expList.Count + 3); xlWorkSheet.get_Range(rang).Cells.Font.Name = "Comic Sans MS"; xlWorkSheet.Range[rang].Font.Size = 16; xlWorkSheet.Range[rang].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink); xlWorkSheet.Range[rang].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); xlWorkSheet.Range[rang].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.Cells[2, 1] = LB_Expense.Content; xlWorkSheet.Cells[2, 2] = LB_Cost.Content; xlWorkSheet.Cells[2, 3] = LB_Count.Content; xlWorkSheet.Cells[2, 4] = (rm as ResourceManager).GetString("LB_Amount"); int sum = 0; for (int i = 0; i < expList.Count; i++) { xlWorkSheet.Cells[3 + i, 1] = expList[i].ExpenseName.Trim();; xlWorkSheet.Cells[3 + i, 2] = f.StringCurrencyFormat(expList[i].Expense.ToString()); xlWorkSheet.Cells[3 + i, 3] = f.StringCurrencyFormat(expList[i].Count.ToString()); xlWorkSheet.Cells[3 + i, 4] = f.StringCurrencyFormat((expList[i].Expense * expList[i].Count).ToString()); xlWorkSheet.Cells[3 + i, 2].NumberFormat = "0"; xlWorkSheet.Cells[3 + i, 3].NumberFormat = "0"; xlWorkSheet.Cells[3 + i, 4].NumberFormat = "0"; sum += expList[i].Expense * expList[i].Count; } xlWorkSheet.Range["A" + (expList.Count + 3), "C" + (expList.Count + 3)].Merge(); xlWorkSheet.Cells[expList.Count + 3, 1] = (rm as ResourceManager).GetString("LB_Amount"); xlWorkSheet.Cells[expList.Count + 3, 4] = f.StringCurrencyFormat(sum.ToString()); xlWorkSheet.get_Range(rang).Columns.AutoFit(); rang = "A" + (expList.Count + 3) + ":" + "D" + (expList.Count + 3); formatRange = xlWorkSheet.get_Range(rang); formatRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); xlWorkSheet.Range[rang].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range[rang].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; xlApp.DisplayAlerts = false; Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog(); int venlenght = exitems[CB_Offer.SelectedIndex].Venue.Length; int addlenght = exitems[CB_Offer.SelectedIndex].Address.Length; saveFileDialog.FileName = (rm as ResourceManager).GetString("LB_Expenses") + "_" + exitems[CB_Offer.SelectedIndex].Venue.Substring(0, venlenght < 15 ? venlenght:15) + "_" + exitems[CB_Offer.SelectedIndex].Address.Substring(0, addlenght < 15 ? addlenght:15); saveFileDialog.Filter = (rm as ResourceManager).GetString("SaveFileDialogFilter"); saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = (rm as ResourceManager).GetString("SaveFileDialogTitle"); if (saveFileDialog.ShowDialog() == true) { System.IO.FileInfo file = new System.IO.FileInfo(saveFileDialog.FileName); if (f.IsFileLocked(file, (rm as ResourceManager), ResourceNames) == false) { xlWorkBook.SaveAs(saveFileDialog.FileName); ViewModel.WinMessageBoxItem wmsgbi = new ViewModel.WinMessageBoxItem((rm as ResourceManager).GetString("MessageBoxSaveTitle"), (rm as ResourceManager).GetString("MessageBoxSaveText"), MaterialDesignThemes.Wpf.PackIconKind.InformationCircle); Windows.WinMessageBox wmsg = new Windows.WinMessageBox(wmsgbi, (rm as ResourceManager), ResourceNames, false); wmsg.Show(); } } xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } catch (Exception ex) { System.Windows.Input.Mouse.OverrideCursor = null; ViewModel.WinMessageBoxItem wmsb = new ViewModel.WinMessageBoxItem("Error", ex.Message, MaterialDesignThemes.Wpf.PackIconKind.Error); Windows.WinMessageBox msb = new Windows.WinMessageBox(wmsb, (rm as ResourceManager), ResourceNames, false); msb.Show(); xlWorkBook.Close(false, Type.Missing, Type.Missing); xlApp.Quit(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); } System.Windows.Input.Mouse.OverrideCursor = null; }