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