Web、winform打印、批量打印解决方案、excel套打模板

点击下载Excel 快递单套打模板

 

在网上找了很多都是.cab 或activex之类的控件比较麻烦、报表又不会，只能用excel了

 

需求很简单,勾选多个订单后,能够批量打印每一个订单的商品列表,由于是在Web上,我的解决方案如下：

 

1，服务器生成Excel 供客户端下载打印(即本地打印)。

2，服务器自动打印(远程打印)。

 

本地打印:

1，服务器生成excel；

2，下载

3，打印

 

远程打印:

1，做一个目录监听程序；

2，生成excel；

3，目录监听程序调用excel,开始打印

 

这样远近基本都满足了,唯一一点就是负责打印的机器需要安装excel

 

以下附上一个excel操作的类:

如下调用即可:

ExcelControl  _excelApp = new ExcelControl();/*实例化*/

ExcelApp.SavefilePath = filePath;/*生成文件存放的位置*/
ExcelApp._SaveToExcel(/*这里传入datatable、或dataset*/）;

 

 

using System; using System.Data; using Microsoft.Office.Interop.Excel; using System.Runtime.InteropServices; using System.Reflection; using System.Diagnostics; using System.Collections.Generic; using System.Text.RegularExpressions; namespace SEDNL { public enum DataFileType { csv, txt, xml, xlsx, xls, html, pdf, xps } /// <summary> /// Excel操作 /// 若为web调用,需在webconfig配置<identity impersonate="true" userName="uname" password="pwd"/> /// </summary> public class ExcelControl { #region 结束进程 protected static void killExcel(_Application excel) { try { Process[] ps = Process.GetProcesses(); foreach (Process p in ps) { if (p.ProcessName.ToLower().Equals("excel")) { p.Kill(); } } } catch (Exception ex) { throw new Exception(ex.Message); } } #endregion private String _savefilePath=String.Empty; public String SavefilePath { get { return _savefilePath; } set { _savefilePath = value; } } public ExcelControl() { } public ExcelControl(String saveFilePath) { this.SavefilePath = saveFilePath; } public void _SaveToExcel(System.Data.DataTable[] dts, Action<Worksheet> addedSheetAction) { SaveToExcel(dts, null, null, addedSheetAction); } public void _SaveToExcel(System.Data.DataSet dt, Action<Worksheet> addedSheetAction) { SaveToExcel(dt, null, null, addedSheetAction); } public void _SaveToExcel(System.Data.DataTable dt, Action<Worksheet> addedSheetAction) { SaveToExcel(dt, null, null, addedSheetAction); } /// <summary> /// 从datatable数组转换 /// </summary> /// <param name="dtArr"></param> public void SaveToExcel(System.Data.DataTable[] dtArr) { SaveToExcel(dtArr, null, null,null); } public void SaveToExcel(System.Data.DataTable[] dtArr, Action<Range> addedColumnsAction) { SaveToExcel(dtArr, addedColumnsAction, null,null); } public void SaveToExcel(System.Data.DataTable[] dts, Action<Range> addedColumnsAction, Action<Range> addedRowsAction) { SaveToExcel(dts, addedColumnsAction, addedRowsAction,null); } public void SaveToExcel(System.Data.DataTable[] dts, Action<Range> addedColumnsAction, Action<Range> addedRowsAction, Action<Worksheet> addedSheetAction) { DataSet ds = new DataSet(); foreach (System.Data.DataTable dt in dts) ds.Tables.Add(dt); SaveToExcel(ds, addedColumnsAction, addedRowsAction,addedSheetAction); } /// <summary> /// 单datatable转换 /// </summary> /// <param name="dt"></param> public void SaveToExcel(System.Data.DataTable dt) { SaveToExcel(dt, null, null, null); } public void SaveToExcel(System.Data.DataTable dt, Action<Range> addedColumnsAction) { SaveToExcel(dt, addedColumnsAction, null, null); } public void SaveToExcel(System.Data.DataTable dt, Action<Range> addedColumnsAction, Action<Range> addedRowsAction) { SaveToExcel(dt, addedColumnsAction, addedRowsAction,null); } public void SaveToExcel(System.Data.DataTable dt, Action<Range> addedColumnsAction, Action<Range> addedRowsAction, Action<Worksheet> addedSheetAction) { DataSet ds = new DataSet(); ds.Tables.Add(dt); SaveToExcel(ds, addedColumnsAction, addedRowsAction,addedSheetAction); } /// <summary> ///从datataset转换 /// </summary> public void SaveToExcel(System.Data.DataSet ds) { SaveToExcel(ds, null, null,null); } public void SaveToExcel(System.Data.DataSet ds, Action<Range> addedColumnsAction) { SaveToExcel(ds, addedColumnsAction, null,null); } public void SaveToExcel(System.Data.DataSet ds, Action<Range> addedColumnsAction, Action<Range> addedRowsAction) { SaveToExcel(ds, addedColumnsAction, addedRowsAction, null); } public void SaveToExcel(System.Data.DataSet ds,Action<Range> addedColumnsAction,Action<Range> addedRowsAction,Action<Worksheet> addedSheetAction) { Application excel=null; try { excel = new Application(); excel.Visible = false; excel.DisplayAlerts = false; Workbook wkb = excel.Workbooks.Add(Missing.Value); for (Int32 i = 3; i < ds.Tables.Count; i++) { wkb.Sheets.Add(); } for (Int32 i = 0; i < ds.Tables.Count; i++) { Worksheet wks = (Worksheet)wkb.Sheets[i + 1]; wks.Name = ds.Tables[i].TableName; Int32 rowIndex = 1, colIndex = 0; foreach (System.Data.DataColumn col in ds.Tables[i].Columns) { colIndex++; wks.Cells[1, colIndex] = col.ColumnName; } /*全部列添加完毕,条件成立就执行委托*/ if (addedColumnsAction != null) { Range row = (Range)wks.Rows[rowIndex]; Range _range = row.get_Range(row.Cells[Missing.Value, 1], row.Cells[Missing.Value, ds.Tables[i].Columns.Count]); addedColumnsAction.Invoke(_range); } foreach (System.Data.DataRow row in ds.Tables[i].Rows) { rowIndex++; colIndex = 0; foreach (System.Data.DataColumn col in ds.Tables[i].Columns) { colIndex++; wks.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } /*当前行添加完毕,条件成立就执行委托*/ if (addedRowsAction != null) { Range _row = (Range)wks.Rows[rowIndex]; Range _range = _row.get_Range(_row.Cells[Missing.Value, 1], _row.Cells[Missing.Value, ds.Tables[i].Columns.Count]); addedRowsAction.Invoke(_range); } } if (addedSheetAction != null) addedSheetAction.Invoke((Worksheet)wkb.Sheets[i + 1]); } wkb.Saved = true; wkb.SaveCopyAs(SavefilePath); wkb.Close(false, Missing.Value, Missing.Value); excel.Quit(); killExcel(excel); } finally { killExcel(excel); } } /// <summary> /// Windows 打印（弹出窗口） /// </summary> /// <param name="filePath">文件路径</param> /// <param name="mode">1:弹窗 0:直接打印</param> public void PrintExcel(String filePath,Int32 mode) { try { foreach (Process p in Process.GetProcesses()) { if (p.ProcessName.ToLower().Equals("excel")) { p.Kill(); return; } } Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application(); excelapp.Visible = false; excelapp.DisplayAlerts = true; Workbook book = excelapp.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); if (mode == 1) excelapp.Dialogs.Item[XlBuiltInDialog.xlDialogPrint].Show(); else { book.PrintOutEx(); } excelapp.Workbooks.Close(); excelapp.Quit(); } catch { } } //字母转数字 public int get_Index(string columnName) { if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) throw new Exception("Invalid parameter"); int index = 0; char[] chars = columnName.ToUpper().ToCharArray(); for (int i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1); } return index; } //数字转字母 public string get_Name(int index) { if (index <= 0) throw new Exception("invaild parameter"); index--; List<string> chars = new List<string>(); do { if (chars.Count > 0) index--; chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString()); index = (int)((index - index % 26) / 26); } while (index > 0); return String.Join(string.Empty, chars.ToArray()); } } }

 

关于目录监用到一个类fileSystemWatcher,自己查查就知道了..

 

监听程序用到了devexpress控件,体积比较大,需要的可以点击这里下载