From 7471f7ba4991636fa75d8f474687815eee583a21 Mon Sep 17 00:00:00 2001
From: duhe <226547893@qq.com>
Date: 星期三, 31 五月 2023 16:20:29 +0800
Subject: [PATCH] 引出功能修复
---
WorkM/报表分析/Sc_ICMOWorkCompleteSet.cs | 4
DBUtility/基础资料/Gy_BaseFun.cs | 404 +++++++++++++++++++++++++++++++++++++-------------------
2 files changed, 267 insertions(+), 141 deletions(-)
diff --git "a/DBUtility/\345\237\272\347\241\200\350\265\204\346\226\231/Gy_BaseFun.cs" "b/DBUtility/\345\237\272\347\241\200\350\265\204\346\226\231/Gy_BaseFun.cs"
index e4192f1..0a0c430 100644
--- "a/DBUtility/\345\237\272\347\241\200\350\265\204\346\226\231/Gy_BaseFun.cs"
+++ "b/DBUtility/\345\237\272\347\241\200\350\265\204\346\226\231/Gy_BaseFun.cs"
@@ -652,79 +652,142 @@
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void DataToExcel(string sText, DataGridView grdMain)
{
- string saveFileName = "";
- bool fileSaved = false;
- SaveFileDialog saveDialog = new SaveFileDialog();
- Excel.Application xlApp = new Excel.Application();
- if (Convert.ToDouble(xlApp.Version) < 12)
+ //string saveFileName = "";
+ //bool fileSaved = false;
+ //SaveFileDialog saveDialog = new SaveFileDialog();
+ //Excel.Application xlApp = new Excel.Application();
+ //if (Convert.ToDouble(xlApp.Version) < 12)
+ //{
+ // saveDialog.DefaultExt = "xls";
+ // saveDialog.Filter = "Excel文件|*.xls";
+ //}
+ //else
+ //{
+ // saveDialog.DefaultExt = "xlsx";
+ // saveDialog.Filter = "Excel文件|*.xlsx";
+ //}
+ //saveDialog.FileName = sText;
+ //saveDialog.ShowDialog();
+ //saveFileName = saveDialog.FileName;
+ //if (saveFileName.IndexOf(":") < 0) return; //被点了取消
+ ////
+
+ //if (xlApp == null)
+ //{
+ // MessageBox.Show("无法创建Excel文件，可能您的电脑未安装Excel", "提示");
+ // return;
+ //}
+
+ //Excel.Workbooks workbooks = xlApp.Workbooks;
+ //Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
+ //Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
+ ////写入 标题
+ //for (int i = 0; i < grdMain.ColumnCount; i++)
+ //{
+ // worksheet.Cells[1, i + 1] = ClsPub.isStrNull(grdMain.Columns[i].HeaderText);
+ //}
+ ////写入内容
+ //for (int r = 0; r < grdMain.RowCount; r++)
+ //{
+ // for (int i = 0; i < grdMain.ColumnCount; i++)
+ // {
+ // worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
+ // }
+ // System.Windows.Forms.Application.DoEvents();
+ //}
+ //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
+ //if (saveFileName != "")
+ //{
+ // try
+ // {
+ // workbook.Saved = true;
+ // workbook.SaveCopyAs(saveFileName);
+ // fileSaved = true;
+ // }
+ // catch (Exception ex)
+ // {
+ // fileSaved = false;
+ // MessageBox.Show( "导出文件时出错,文件可能正被打开！\n" + ex.Message, "提示");
+ // }
+ //}
+ //else
+ //{
+ // fileSaved = false;
+ //}
+ //xlApp.Quit();
+ ////
+ //IntPtr t = new IntPtr(xlApp.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
+ //int k = 0;
+ //GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
+ //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
+ //p.Kill(); //关闭进程k
+ //xlApp = null;
+ ////GC.Collect();//强行销毁
+ ////if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
+ //MessageBox.Show("引出完毕！");
+ SaveFileDialog saveFileDialog = new SaveFileDialog();
+ saveFileDialog.Filter = "Execl文件(*.xls)|*.xls";
+ saveFileDialog.FilterIndex = 0;
+ saveFileDialog.RestoreDirectory = true;
+ saveFileDialog.CreatePrompt = true;
+ saveFileDialog.Title = "数据视图导出EXCEL文件";
+ saveFileDialog.ShowDialog();
+ if (saveFileDialog.FileName == "")
{
- saveDialog.DefaultExt = "xls";
- saveDialog.Filter = "Excel文件|*.xls";
- }
- else
- {
- saveDialog.DefaultExt = "xlsx";
- saveDialog.Filter = "Excel文件|*.xlsx";
- }
- saveDialog.FileName = sText;
- saveDialog.ShowDialog();
- saveFileName = saveDialog.FileName;
- if (saveFileName.IndexOf(":") < 0) return; //被点了取消
- //
-
- if (xlApp == null)
- {
- MessageBox.Show("无法创建Excel文件，可能您的电脑未安装Excel", "提示");
return;
}
-
- Excel.Workbooks workbooks = xlApp.Workbooks;
- Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
- Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
- //写入 标题
- for (int i = 0; i < grdMain.ColumnCount; i++)
+ Stream myStream;
+ myStream = saveFileDialog.OpenFile();
+ StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
+ string str = "";
+ DateTime start = DateTime.Now;
+ try
{
- worksheet.Cells[1, i + 1] = ClsPub.isStrNull(grdMain.Columns[i].HeaderText);
- }
- //写入内容
- for (int r = 0; r < grdMain.RowCount; r++)
- {
+ //写标题
for (int i = 0; i < grdMain.ColumnCount; i++)
{
- worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
+ if (i > 0)
+ {
+ str += "\t";
+ }
+ str += grdMain.Columns[i].HeaderText;
}
- System.Windows.Forms.Application.DoEvents();
- }
- worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
- if (saveFileName != "")
- {
- try
+ sw.WriteLine(str);
+
+ //写内容
+ for (int j = 0; j < grdMain.Rows.Count; j++)
{
- workbook.Saved = true;
- workbook.SaveCopyAs(saveFileName);
- fileSaved = true;
+ string tempStr = "";
+ for (int k = 0; k < grdMain.Columns.Count; k++)
+ {
+ if (k > 0)
+ {
+ tempStr += "\t";
+ }
+ if (grdMain.Rows[j].Cells[k].Value == null)
+ {
+ tempStr += string.Empty;
+ }
+ else
+ {
+ tempStr += grdMain.Rows[j].Cells[k].Value.ToString().Replace("\"", "”");
+ }
+ }
+ sw.WriteLine(tempStr);
}
- catch (Exception ex)
- {
- fileSaved = false;
- MessageBox.Show( "导出文件时出错,文件可能正被打开！\n" + ex.Message, "提示");
- }
+ sw.Close();
+ myStream.Close();
}
- else
+ catch (Exception ex)
{
- fileSaved = false;
+ MessageBox.Show(ex.Message);
}
- xlApp.Quit();
- //
- IntPtr t = new IntPtr(xlApp.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
- int k = 0;
- GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
- System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
- p.Kill(); //关闭进程k
- xlApp = null;
- //GC.Collect();//强行销毁
- //if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
- MessageBox.Show("引出完毕！");
+ finally
+ {
+ sw.Close();
+ myStream.Close();
+ }
+ MessageBox.Show("将此工作表导出为excel共耗时：" + DateTime.Now.Subtract(start).TotalMilliseconds.ToString() + "毫秒");
}
@@ -957,99 +1020,162 @@
//导出选中列
public static void Data2Excel(string sText, DataGridView grdMain, string KeyItem)
{
- string saveFileName = "";
- bool fileSaved = false;
- List<string> hide = GetGridViewHide(grdMain, KeyItem);
- if(hide.Count == 0)
- {
- DataToExcel(sText, grdMain);
- return;
- }
- SaveFileDialog saveDialog = new SaveFileDialog();
- Excel.Application xlApp = new Excel.Application();
- if (xlApp == null)
- {
- MessageBox.Show("无法创建Excel文件，可能您的电脑未安装Excel", "提示");
- return;
- }
- string av = xlApp.Version;
- if (Convert.ToDouble(xlApp.Version) < 12)
- {
- saveDialog.DefaultExt = "xls";
- saveDialog.Filter = "Excel文件|*.xls";
- }
- else
- {
- saveDialog.DefaultExt = "xlsx";
- saveDialog.Filter = "Excel文件|*.xlsx";
- }
- saveDialog.FileName = sText;
- saveDialog.ShowDialog();
- saveFileName = saveDialog.FileName;
- if (saveFileName.IndexOf(":") < 0) return; //被点了取消
-
+ //string saveFileName = "";
+ //bool fileSaved = false;
+ //List<string> hide = GetGridViewHide(grdMain, KeyItem);
+ //if(hide.Count == 0)
+ //{
+ // DataToExcel(sText, grdMain);
+ // return;
+ //}
+ //SaveFileDialog saveDialog = new SaveFileDialog();
+ //Excel.Application xlApp = new Excel.Application();
+ //if (xlApp == null)
+ //{
+ // MessageBox.Show("无法创建Excel文件，可能您的电脑未安装Excel", "提示");
+ // return;
+ //}
+ // string av = xlApp.Version;
+ // if (Convert.ToDouble(xlApp.Version) < 12)
+ // {
+ // saveDialog.DefaultExt = "xls";
+ // saveDialog.Filter = "Excel文件|*.xls";
+ // }
+ // else
+ // {
+ // saveDialog.DefaultExt = "xlsx";
+ // saveDialog.Filter = "Excel文件|*.xlsx";
+ // }
+ //saveDialog.FileName = sText;
+ //saveDialog.ShowDialog();
+ //saveFileName = saveDialog.FileName;
+ //if (saveFileName.IndexOf(":") < 0) return; //被点了取消
-
- Excel.Workbooks workbooks = xlApp.Workbooks;
- Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
- Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
- //写入 标题
- for (int i = 0; i < hide.Count; i++)
- {
- worksheet.Cells[1, i + 1] = hide[i];
- }
- for (int r = 0; r < grdMain.RowCount; r++)
- {
- for (int i = 0; i < hide.Count; i++)
- {
- worksheet.Cells[r + 2, i + 1] = "" + ClsPub.isStrNull(grdMain.Rows[r].Cells[DBUtility.Gy_BaseFun.Fun_GetCol(hide[i], grdMain)].Value);
- //worksheet.Cells[r + 2, i + 1] = grdMain.Rows[r].Cells[DBUtility.Gy_BaseFun.Fun_GetCol(hide[i], grdMain)].Value;
- }
- System.Windows.Forms.Application.DoEvents();
- }
-
+ //Excel.Workbooks workbooks = xlApp.Workbooks;
+ //Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
+ //Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
+ ////写入 标题
+ //for (int i = 0; i < hide.Count; i++)
+ //{
+ // worksheet.Cells[1, i + 1] = hide[i];
+ //}
+
//for (int r = 0; r < grdMain.RowCount; r++)
//{
- // for (int i = 0; i < grdMain.ColumnCount; i++)
+ // for (int i = 0; i < hide.Count; i++)
// {
- // worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
+
+ // worksheet.Cells[r + 2, i + 1] = "" + ClsPub.isStrNull(grdMain.Rows[r].Cells[DBUtility.Gy_BaseFun.Fun_GetCol(hide[i], grdMain)].Value);
+ // //worksheet.Cells[r + 2, i + 1] = grdMain.Rows[r].Cells[DBUtility.Gy_BaseFun.Fun_GetCol(hide[i], grdMain)].Value;
// }
// System.Windows.Forms.Application.DoEvents();
//}
+ ////for (int r = 0; r < grdMain.RowCount; r++)
+ ////{
+ //// for (int i = 0; i < grdMain.ColumnCount; i++)
+ //// {
+ //// worksheet.Cells[r + 2, i + 1] = "'" + ClsPub.isStrNull(grdMain.Rows[r].Cells[i].Value);
+ //// }
+ //// System.Windows.Forms.Application.DoEvents();
+ ////}
- worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
- if (saveFileName != "")
+
+ //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
+ //if (saveFileName != "")
+ //{
+ // try
+ // {
+ // workbook.Saved = true;
+ // workbook.SaveCopyAs(saveFileName);
+ // fileSaved = true;
+ // }
+ // catch (Exception ex)
+ // {
+ // fileSaved = false;
+ // MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message, "提示");
+ // }
+ //}
+ //else
+ //{
+ // fileSaved = false;
+ //}
+ //xlApp.Quit();
+ ////
+ //IntPtr t = new IntPtr(xlApp.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
+ //int k = 0;
+ //GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
+ //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
+ //p.Kill(); //关闭进程k
+ //xlApp = null;
+ ////GC.Collect();//强行销毁
+ ////if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
+ //MessageBox.Show("引出完毕！");
+ SaveFileDialog saveFileDialog = new SaveFileDialog();
+ saveFileDialog.Filter = "Execl文件(*.xls)|*.xls";
+ saveFileDialog.FilterIndex = 0;
+ saveFileDialog.RestoreDirectory = true;
+ saveFileDialog.CreatePrompt = true;
+ saveFileDialog.Title = "数据视图导出EXCEL文件";
+ saveFileDialog.ShowDialog();
+ if (saveFileDialog.FileName == "")
{
- try
- {
- workbook.Saved = true;
- workbook.SaveCopyAs(saveFileName);
- fileSaved = true;
- }
- catch (Exception ex)
- {
- fileSaved = false;
- MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message, "提示");
- }
+ return;
}
- else
+ Stream myStream;
+ myStream = saveFileDialog.OpenFile();
+ StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
+ string str = "";
+ DateTime start = DateTime.Now;
+ try
{
- fileSaved = false;
+ //写标题
+ for (int i = 0; i < grdMain.ColumnCount; i++)
+ {
+ if (i > 0)
+ {
+ str += "\t";
+ }
+ str += grdMain.Columns[i].HeaderText;
+ }
+ sw.WriteLine(str);
+
+ //写内容
+ for (int j = 0; j < grdMain.Rows.Count; j++)
+ {
+ string tempStr = "";
+ for (int k = 0; k < grdMain.Columns.Count; k++)
+ {
+ if (k > 0)
+ {
+ tempStr += "\t";
+ }
+ if (grdMain.Rows[j].Cells[k].Value == null)
+ {
+ tempStr += string.Empty;
+ }
+ else
+ {
+ tempStr += grdMain.Rows[j].Cells[k].Value.ToString().Replace("\"", "”");
+ }
+ }
+ sw.WriteLine(tempStr);
+ }
+ sw.Close();
+ myStream.Close();
}
- xlApp.Quit();
- //
- IntPtr t = new IntPtr(xlApp.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
- int k = 0;
- GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
- System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
- p.Kill(); //关闭进程k
- xlApp = null;
- //GC.Collect();//强行销毁
- //if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
- MessageBox.Show("引出完毕！");
+ catch (Exception ex)
+ {
+ MessageBox.Show(ex.Message);
+ }
+ finally
+ {
+ sw.Close();
+ myStream.Close();
+ }
+ MessageBox.Show("将此工作表导出为excel共耗时：" + DateTime.Now.Subtract(start).TotalMilliseconds.ToString() + "毫秒");
}
diff --git "a/WorkM/\346\212\245\350\241\250\345\210\206\346\236\220/Sc_ICMOWorkCompleteSet.cs" "b/WorkM/\346\212\245\350\241\250\345\210\206\346\236\220/Sc_ICMOWorkCompleteSet.cs"
index 9cd9505..0359e62 100644
--- "a/WorkM/\346\212\245\350\241\250\345\210\206\346\236\220/Sc_ICMOWorkCompleteSet.cs"
+++ "b/WorkM/\346\212\245\350\241\250\345\210\206\346\236\220/Sc_ICMOWorkCompleteSet.cs"
@@ -334,8 +334,8 @@
{
if (tabControl1.SelectedIndex == 0)
{
- //DBUtility.Gy_BaseFun.DataToExcel(this.Text, grdMain);
- DBUtility.Gy_BaseFun.DataGridViewToExcel2(grdMain);
+ DBUtility.Gy_BaseFun.DataToExcel(this.Text, grdMain);
+ //DBUtility.Gy_BaseFun.DataGridViewToExcel2(grdMain);
}
else
{
--
Gitblit v1.9.1