private void timer1_Tick(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value; //定义object缺省值
string[] P_str_Names = txt_MultiExcel.Text.Split(','); //存储所有选择的Excel文件名
string P_str_Name = ""; //存储遍历到的Excel文件名
List <string> P_list_SheetNames = new List <string>(); //实例化泛型集合对象，用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
if (DateTime.Now.Hour == nudown_Hour.Value && DateTime.Now.Minute == nudown_Min.Value)
{
for (int i = 0; i < P_str_Names.Length - 1; i++) //遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i]; //记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name); //获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++) //遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]]; //创建新工作表
TempWorksheet.Copy(missing, newWorksheet); //将工作表内容复制到目标工作表中
}
Tempworkbook.Close(false, missing, missing); //关闭临时工作簿
}
}
workbook.Save(); //保存目标工作簿
workbook.Close(false, missing, missing); //关闭目标工作簿
MessageBox.Show("程序在" + DateTime.Now.ToShortTimeString() + "分时自动汇总了多个Excel文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL"); //关闭所有Excel进程
}
public void PrintArrayToSheetTemplate(String[,] arr, string sheetName)
{
if ((excelbook != null) & (arr.GetLength(0) > 0))
{
Microsoft.Office.Interop.Excel.Worksheet destSheet = null;
//----- Добавление листа на базе шаблона
if (!(IsSheetExist(sheetName)))
{
destSheet = excelbook.Worksheets.get_Item("template");
destSheet.Copy(excelbook.Worksheets.get_Item("template"));
destSheet = excelbook.Worksheets.get_Item("template (2)");
destSheet.Name = sheetName;
}
else
{
destSheet = excelbook.Worksheets.get_Item(sheetName);
}
destSheet.Select();
destSheet.UsedRange.Clear();
var rng = destSheet.get_Range("A1", System.Reflection.Missing.Value).get_Resize(arr.GetLength(0), arr.GetLength(1));
rng.set_Value(System.Reflection.Missing.Value, arr);
OnReportMessage("Выгружены данные в лист " + sheetName);
}
}
/// <summary>
/// Excel 转换成 HTML 文件
/// </summary>
/// <param name="excelPath">Excel 文档路径</param>
/// <param name="htmlPath">Html 文件路径</param>
/// <param name="sheetIndex">表单索引，如果大于 0，则按指定的 Sheet 生成 HTML</param>
public static void ExcelToHtmlFile(string excelPath, string htmlPath, int sheetIndex = 0)
{
if (string.IsNullOrEmpty(excelPath))
{
throw new Exception(ExcelPathNullException);
}
//实例化Excel
Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
//打开文件，n.FullPath是文件路径
Microsoft.Office.Interop.Excel.Workbook workbook = application.Application.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string directoryPath = Path.GetDirectoryName(htmlPath);
string fileDirectoryPath = Path.Combine(directoryPath, Path.GetFileNameWithoutExtension(htmlPath));
Microsoft.Office.Interop.Excel.Workbook newWorkbook = null;
if (sheetIndex > 0)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sheetIndex];
newWorkbook = application.Application.Workbooks.Add(1);
worksheet.Copy(newWorkbook.Sheets[1]);
((Microsoft.Office.Interop.Excel.Worksheet)newWorkbook.Worksheets[2]).Delete();
//进行另存为操作
newWorkbook.SaveAs(htmlPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
//进行另存为操作
workbook.SaveAs(htmlPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
//逐步关闭所有使用的对象
workbook.Close(false, Type.Missing, Type.Missing);
if (newWorkbook != null)
{
newWorkbook.Close(false, Type.Missing, Type.Missing);
}
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
if (newWorkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorkbook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(application.Application.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
if (sheetIndex > 0)
{
TransformHTMLEncoding(htmlPath, string.Format("<frame src=\"{0}.files/tabstrip.html\" name=\"frTabs\" marginwidth=0 marginheight=0>", Path.GetFileNameWithoutExtension(htmlPath)));
}
System.Diagnostics.Process[] processList = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process process in processList)
{
process.Kill();
}
}
public ArrayList DoPrint()
{
//根据配置文件读取有关excel模版的信息
_excelName = IniReadValue("模版信息", _printType);
_excelWindowsName = "Microsoft Excel - " + _excelName + " [只读]";
_excelPath = Application.StartupPath + "\\" + _excelName;
//当条形码打印成功后增加到arraylist里返回到调用方设置已打印标志
ArrayList _arPrinted = new ArrayList();
try
{
if (_printData == null || _printData.Tables.Count == 0 || _printData.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无打印数据!", "提示");
CloseExcel();
return(_arPrinted);
}
//检验是否取到模版信息，否则退出
if (_excelName == "" || _excelWindowsName == "" || _excelPath == "")
{
MessageBox.Show("模版信息维护错误！", "提示");
CloseExcel();
return(_arPrinted);
}
//判断excel模版是否已经被打开，打开excel模版
IntPtr a = FindWindow(null, _excelWindowsName);
if (a.ToString() != "0")
{
MessageBox.Show("Excel模版已经被打开,可能是正在打印或人为打开。\n请确认没有执行打印程序,然后手动关闭该EXCEL文档。", "提示");
CloseExcel();
return(_arPrinted);
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;
excel.Top = 8000;
excel.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);
string fileName = _excelPath;
workbook = excel.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.ReadOnlyRecommended = true;
Int32 sheetNum1 = 0;
try
{
sheetNum1 = Convert.ToInt32(IniReadValue(_printType, "FIELDNUM"));
}
catch
{
MessageBox.Show("配置文件-" + _printType + "-FIELDNUM，维护错误");
CloseExcel();
return(_arPrinted);
}
Hashtable hs = new Hashtable();
Hashtable hs2 = new Hashtable();
for (Int32 s = 1; s < sheetNum1 + 1; s++)
{
string FieldValue = IniReadValue(_printType, "F" + s.ToString());
hs.Add("F" + s.ToString(), FieldValue);
string FieldCell = IniReadValue(_printType, "C" + s.ToString());
hs2.Add("C" + s.ToString(), FieldCell);
}
_iniTable.Add(_printType, hs);
_iniCell.Add(_printType, hs2);
//针对每一行数据生成数据表
Int32 num = _printData.Tables[0].Rows.Count;
sh2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType);
for (int i = 1; i < num + 1; i++)
{
if (i == 1)
{
sh3 = sh2;
}
else
{
sh3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType + " (" + Convert.ToString(i) + ")");
}
sh2.Copy(Type.Missing, sh3);
}
string Barcode = "";
for (int i = 0; i < num; i++)
{
//根据配置文件设置excel中的数据
DataRow r = _printData.Tables[0].Rows[i];
Barcode = r["条码号"].ToString();
string sheet = "";
sheet = _printType + " (" + Convert.ToString(i + 2) + ")";
try
{
GetWorkSheet(sheet);
}
catch { MessageBox.Show("找不到打印模版表" + sheet, "提示"); return(_arPrinted); }
Hashtable ht = (Hashtable)_iniTable[_printType];
Hashtable ht2 = (Hashtable)_iniCell[_printType];
foreach (DictionaryEntry de in ht)
{
try
{
SetCellRangeValue(ht2["C" + de.Key.ToString().Substring(1)].ToString(), r[de.Value.ToString()].ToString());
}
catch { }
}
SetCellRangeValue("A10", r["条码号"].ToString());
_arPrinted.Add(r["条码号"].ToString());
}
//调用VBA宏生成条形码
object robj = new object();
try
{
RunExcelMacro(_excelPath, "getTime3", new Object[] { "" }, out robj, true);
}
catch
{
MessageBox.Show("执行宏失败！", "提示");
CloseExcel();
return(_arPrinted);
}
Thread.Sleep(1000);
//打印输出
try
{
for (int i = 0; i < Convert.ToInt32(_printCopies); i++)
{
workbook.Worksheets.PrintOut(2, num + 1, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
catch
{
MessageBox.Show("连接打印机失败，请检查操作系统中默认打印机是否运行正常！", "提示");
_arPrinted.Clear();
CloseExcel();
return(_arPrinted);
}
CloseExcel();
return(_arPrinted);
}
catch
{
CloseExcel();
return(_arPrinted);
}
}
public ArrayList DoPrint()
{
//根据配置文件读取有关excel模版的信息
_excelName = IniReadValue("模版信息", _printType);
_excelWindowsName = "Microsoft Excel - " + _excelName+" [只读]";
_excelPath = Application.StartupPath + "\\" + _excelName;
//当条形码打印成功后增加到arraylist里返回到调用方设置已打印标志
ArrayList _arPrinted = new ArrayList();
try
{
if (_printData == null || _printData.Tables.Count == 0 || _printData.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无打印数据!", "提示");
CloseExcel();
return _arPrinted;
}
//检验是否取到模版信息，否则退出
if (_excelName == "" || _excelWindowsName == "" || _excelPath == "")
{
MessageBox.Show("模版信息维护错误！", "提示");
CloseExcel();
return _arPrinted;
}
//判断excel模版是否已经被打开，打开excel模版
IntPtr a = FindWindow(null, _excelWindowsName);
if (a.ToString() != "0")
{
MessageBox.Show("Excel模版已经被打开,可能是正在打印或人为打开。\n请确认没有执行打印程序,然后手动关闭该EXCEL文档。", "提示");
CloseExcel();
return _arPrinted;
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;
excel.Top = 8000;
excel.WorkbookActivate+=new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);
string fileName = _excelPath;
workbook = excel.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,missing, missing);
workbook.ReadOnlyRecommended = true;
Int32 sheetNum1 = 0;
try
{
sheetNum1 = Convert.ToInt32(IniReadValue(_printType, "FIELDNUM"));
}
catch
{
MessageBox.Show("配置文件-" + _printType + "-FIELDNUM，维护错误");
CloseExcel();
return _arPrinted;
}
Hashtable hs = new Hashtable();
Hashtable hs2 = new Hashtable();
for (Int32 s = 1; s < sheetNum1 + 1; s++)
{
string FieldValue = IniReadValue(_printType, "F" + s.ToString());
hs.Add("F" + s.ToString(), FieldValue);
string FieldCell = IniReadValue(_printType, "C" + s.ToString());
hs2.Add("C" + s.ToString(), FieldCell);
}
_iniTable.Add(_printType, hs);
_iniCell.Add(_printType, hs2);
//针对每一行数据生成数据表
Int32 num = _printData.Tables[0].Rows.Count;
sh2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType);
for (int i = 1; i < num+1; i++)
{
if (i == 1)
{
sh3 = sh2;
}
else
{
sh3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(_printType + " (" + Convert.ToString(i) + ")");
}
sh2.Copy(Type.Missing, sh3);
}
string Barcode = "";
for (int i = 0; i < num; i++)
{
//根据配置文件设置excel中的数据
DataRow r = _printData.Tables[0].Rows[i];
Barcode = r["条码号"].ToString();
string sheet = "";
sheet = _printType + " (" + Convert.ToString(i + 2) + ")";
try
{
GetWorkSheet(sheet);
}
catch { MessageBox.Show("找不到打印模版表" + sheet, "提示"); return _arPrinted; }
Hashtable ht = (Hashtable)_iniTable[_printType];
Hashtable ht2 = (Hashtable)_iniCell[_printType];
foreach (DictionaryEntry de in ht)
{
try
{
SetCellRangeValue(ht2["C" + de.Key.ToString().Substring(1)].ToString(), r[de.Value.ToString()].ToString());
}
catch { }
}
SetCellRangeValue("A10", r["条码号"].ToString());
_arPrinted.Add(r["条码号"].ToString());
}
//调用VBA宏生成条形码
object robj = new object();
try
{
RunExcelMacro(_excelPath, "getTime3", new Object[] { "" }, out robj, true);
}
catch
{
MessageBox.Show("执行宏失败！", "提示");
CloseExcel();
return _arPrinted;
}
Thread.Sleep(1000);
//打印输出
try
{
for (int i = 0; i < Convert.ToInt32(_printCopies); i++)
{
workbook.Worksheets.PrintOut(2, num+1, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
catch
{
MessageBox.Show("连接打印机失败，请检查操作系统中默认打印机是否运行正常！", "提示");
_arPrinted.Clear();
CloseExcel();
return _arPrinted;
}
CloseExcel();
return _arPrinted;
}
catch
{
CloseExcel();
return _arPrinted;
}
}
private void btn_Gather_Click(object sender, EventArgs e)
{
try
{
_TempExcel = Path.GetTempPath();
_TempExcel += DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";
object miss = System.Reflection.Missing.Value; //定义object缺省值
string[] P_str_Names = txt_MultiExcel.Text.Split(','); //存储所有选择的Excel文件名
string P_str_Name = ""; //存储遍历到的Excel文件名
List <string> P_list_SheetNames = new List <string>(); //实例化泛型集合对象，用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象[[
excel.Visible = false;
excel.DisplayAlerts = false;
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(miss, miss, miss, miss); //创建新工作表
for (int i = 0; i < P_str_Names.Length - 1; i++) //遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i]; //记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
for (int j = 0; j < Tempworkbook.Sheets.Count; j++)//遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets.get_Item(j + 1);
TempWorksheet.Copy(miss, newWorksheet);//将工作表内容复制到目标工作表中
}
Tempworkbook.Close(false, miss, miss);//关闭临时工作簿
}
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["Sheet1"]).Delete();
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["Sheet2"]).Delete();
workbook.SaveAs(_TempExcel, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
workbook.Close(false, miss, miss);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
excel.Workbooks.Close();
excel.Quit();
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorksheet);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel.Workbooks);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
//workbook = null;
//newWorksheet = null;
//excel = null;
// GC.Collect();
}
catch (Exception ex)
{
throw ex;
}
}
