代码,需要加入的控件:PrintDocument、PageSetupDialog、PrintDialog、PrintPreviewDialog、BackgroundWorker,控件的Document属性要指定为PrintDocument控件的id。
导入Excel用的是12.0的驱动,需要下载:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
这是新的64位的:https://www.microsoft.com/en-us/download/details.aspx?id=54920
换行打印:https://blog.csdn.net/knowledgeables/article/details/46521381
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms; namespace PrintLabels
{
public partial class MainForm : Form
{
/// <summary>
/// 消息队列
/// Queue.Peek();//第一个元素; Queue.Dequeue();//移除第一个元素;Queue.Enqueue//添加到 Queue<T> 的末尾
/// </summary>
static Queue<Message> queuePrint = new Queue<Message>();
StringBuilder printResult = new StringBuilder();//打印结果
//int PrintCount = 0;//打印的标签总数
string row1;
string row2;
string row3;
string row4;
string row5;
string row6; public MainForm()
{
InitializeComponent();
this.printDocument1.OriginAtMargins = true;//启用页边距
this.pageSetupDialog1.EnableMetric = true; //以毫米为单位
dataGridView1.AutoGenerateColumns = false;//禁止自动创建列
}
/// <summary>
/// 导入excel表格
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportExcel_Click(object sender, EventArgs e)
{
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "Excel(2007)文件|*.xlsx|Excel(97-2003)文件|*.xls|所有文件|*.*";
fd.Title = "打开文件夹";
string path = "";
//fd.InitialDirectory = "d:\\";
fd.FilterIndex = ;
if (fd.ShowDialog() == DialogResult.OK)
{
path = fd.FileName;
}
if (!string.IsNullOrEmpty(path))
{
printResult.Remove(, printResult.Length);//清除打印结果
DataSet ds = GetExcelTables(path);//读取Excel到DataSet
DataTable dtTotal = ds.Tables["总表$"];
List<TotalModel> totalList = GetTotalData(dtTotal);
List<PurchaseModel> purchaseList = new List<PurchaseModel>();//打印数据
foreach (DataTable dt in ds.Tables)
{
if (!dt.TableName.Contains("总表"))
{
List<PurchaseModel> list = GetPurchaseData(dt, totalList);
if (list.Count > )
{
purchaseList.AddRange(list);
}
}
}
string strmsg = printResult.ToString();
if (strmsg != "")
{
MessageBox.Show(strmsg);
}
else
{
dataGridView1.DataSource = purchaseList;
}
}
} /// <summary>
/// 打印设置
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSetPrint_Click(object sender, EventArgs e)
{
this.pageSetupDialog1.ShowDialog();
}
//打印预览
private void btnPrePrint_Click(object sender, EventArgs e)
{
this.printPreviewDialog1.ShowDialog();
}
//打印DataGridView数据
private void btnPrint_Click(object sender, EventArgs e)
{
if (this.printDialog1.ShowDialog() == DialogResult.OK)
{
if (dataGridView1.Rows[].Cells[].Value != null)
{
btnPrint.Enabled = false; //加入队列
queuePrint.Clear();
List<Message> messages = new List<Message>();
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
if (dr.Cells["DocNo"].Value != null)
{
string docNo = dr.Cells["DocNo"].Value.ToString();
string destination = dr.Cells["Destination"].Value.ToString();
string warehouse = dr.Cells["Warehouse"].Value.ToString();
string proId = dr.Cells["ProId"].Value.ToString().TrimEnd(',').TrimEnd(',');
string proName = dr.Cells["ProName"].Value.ToString().TrimEnd(',').TrimEnd(',');
int boxNum = Convert.ToInt32(dr.Cells["BoxNum"].Value); for (int i = ; i <= boxNum; i++)
{
messages.Add(new Message()
{
Count = ,
PM = new PurchaseModel()
{
BoxNum = boxNum,
CurBoxNum = i,
Destination = destination,
DocNo = docNo,
ProId = proId,
ProName = proName,
Warehouse = warehouse
}
});
}
}
}
Print(messages);//打印
btnPrint.Enabled = true;
}
else
{
//MessageBox.Show("不允许打印空数据");
return;
}
}
}
/// <summary>
/// 直接打印
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPrint2_Click(object sender, EventArgs e)
{
if (this.printDialog1.ShowDialog() == DialogResult.OK)
{
if (dataGridView1.Rows[].Cells[].Value != null)
{
btnPrint.Enabled = false; //加入队列
queuePrint.Clear();
List<Message> messages = new List<Message>();
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
if (dr.Cells["DocNo"].Value != null)
{
string docNo = dr.Cells["DocNo"].Value.ToString();
string destination = dr.Cells["Destination"].Value.ToString();
string warehouse = dr.Cells["Warehouse"].Value.ToString();
string proId = dr.Cells["ProId"].Value.ToString().TrimEnd(',').TrimEnd(',');
string proName = dr.Cells["ProName"].Value.ToString().TrimEnd(',').TrimEnd(',');
int boxNum = Convert.ToInt32(dr.Cells["BoxNum"].Value); for (int i = ; i <= boxNum; i++)
{
messages.Add(new Message()
{
Count = ,
PM = new PurchaseModel()
{
BoxNum = boxNum,
CurBoxNum = i,
Destination = destination,
DocNo = docNo,
ProId = proId,
ProName = proName,
Warehouse = warehouse
}
});
}
}
}
//Print(messages); queuePrint.AddRange(messages);
this.printDocument1.Print(); btnPrint.Enabled = true;
}
else
{
return;
}
}
} #region
/// <summary>
/// 把导入的DataTable数据转化为List
/// </summary>
/// <param name="dt"></param>
/// <param name="totalList"></param>
/// <returns></returns>
public List<PurchaseModel> GetPurchaseData(DataTable dt, List<TotalModel> totalList)
{
string dest = dt.TableName.Replace("$", "");
List<PurchaseModel> list = new List<PurchaseModel>(); for (int i = ; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (dr[].ToString().Contains("采购单号"))
{
string docNo = dr[].ToString();
PurchaseModel purchase = new PurchaseModel();
purchase.Destination = dest;
purchase.DocNo = docNo;
i += ;
for (; i < dt.Rows.Count; i++)
{
bool isok = false;
string proName = "";
string proIdStr = "";
string warehouse = "";
int boxNum = ;
for (; i < dt.Rows.Count; i++)
{
string proId = dt.Rows[i][].ToString();//商品编号
if (!string.IsNullOrEmpty(proId))
{
proIdStr += proId + ",";
proName += dt.Rows[i][].ToString() + ",";//商品名称
warehouse = dt.Rows[i][].ToString();//仓库 int purNum = Convert.ToInt32(dt.Rows[i][].ToString());//采购数量,计算箱数
TotalModel totalModel = totalList.Where(c => c.ProNo == proId).First();
if (totalModel == null)
{
string msg = "表:" + dt.TableName + " 行:" + (i + ) + " 列:1;无法找到对应总表数据,请检查数据格式!\r\n";
printResult.Append(msg);
}
else
{
int packageNum = totalModel.PackageNum;//箱规
boxNum += ((purNum / packageNum) + ((purNum % packageNum) > ? : ));//计算出箱数
}
}
else
{
isok = true;
break;
}
}
purchase.BoxNum = boxNum;
purchase.ProId = proIdStr.TrimEnd(',');
purchase.ProName = proName.TrimEnd(',');
purchase.Warehouse = warehouse; if (isok)
{
break;
}
}
list.Add(purchase);
}
}
return list;
}
/// <summary>
/// 获取总表中箱规数据
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public List<TotalModel> GetTotalData(DataTable dt)
{
List<TotalModel> list = new List<TotalModel>();
for (int i = ; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
string proNo = dr[].ToString();
if (!proNo.Contains("合计"))
{
string pn = dr[dt.Columns.Count - ].ToString();
if (pn != "")
{
int packageNum = Convert.ToInt32(pn);
list.Add(new TotalModel() { ProNo = proNo, PackageNum = packageNum });
}
}
}
return list;
}
/// <summary>
/// 获取Excel数据
/// </summary>
/// <param name="fullPath"></param>
/// <returns></returns>
public DataSet GetExcelTables(string fullPath)
{
DataSet ds = new DataSet();
if (File.Exists(fullPath))
{
//HDR=No 第一行就是数据
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
foreach (DataRow item in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows)
{
string tableName = item[].ToString().Trim();
DataTable dt = new DataTable();
dt.TableName = tableName;
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + tableName + "]", conn);
odda.Fill(dt);
ds.Tables.Add(dt);
}
}
}
return ds;
}
#endregion #region 打印
internal void Print(Message msg)
{
Print(new List<Message>() { msg });
}
internal void Print(IList<Message> list)
{
if (list == null || list.Count < ) return;
queuePrint.AddRange(list);
if (backgroundWorker1.IsBusy == false)
{
backgroundWorker1.RunWorkerAsync();
}
} bool IsPrinting = false;
private void DoPrint()
{
if (IsPrinting) return;
while (queuePrint.Count > )
{
printDocument1.Print();
//重要,循环作业,UI可接收消息
Application.DoEvents();//此处解决过多打印队列会使界面假死问题
}
IsPrinting = false;
}
private void backgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
var worker = sender as BackgroundWorker;
while (queuePrint.Count > )
{
if ((worker.CancellationPending == true))
{
e.Cancel = true;
break;
}
else
{
printDocument1.Print();
System.Threading.Thread.Sleep();
}
}
}
/// <summary>
/// 填充打印主体
/// </summary>
private void fillPrintBody(PurchaseModel lp)
{
if (lp == null) return;
row1 = "供应商名称:xxxxxx";
row2 = "采购单号:" + lp.DocNo;
row3 = "目的城市:" + lp.Destination;
row4 = "仓库:" + lp.Warehouse;
row5 = "商品名称:" + lp.ProId;
row6 = "箱数:第" + lp.CurBoxNum + "箱,共" + lp.BoxNum + "箱";
}
private void printDocument1_QueryPageSettings(object sender, System.Drawing.Printing.QueryPageSettingsEventArgs e) { }
/// <summary>
/// 打印前
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printDocument1_BeginPrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
if (queuePrint.Count < )
{
e.Cancel = true;
return;
}
//printDialog1.PrinterSettings.Copies = 1;// (short)msg.Count; //设置每个只打印一份
var msg = queuePrint.Peek();
fillPrintBody(msg.PM);
}
/// <summary>
/// 打印后
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printDocument1_EndPrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
queuePrint.Dequeue();//移除已被打印的数据
}
/// <summary>
/// 打印文档
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
e.Graphics.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.NearestNeighbor;
//像素偏移方式,像素在水平和垂直距离上均偏移若干个单位,以进行高速锯齿消除。
e.Graphics.PixelOffsetMode = System.Drawing.Drawing2D.PixelOffsetMode.Half;
//也可以通过设置Graphics对不平平滑处理方式解决,代码如下:
e.Graphics.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality; Font drawFont = new Font("宋体", );
Brush drawBrush = Brushes.Black;
e.Graphics.DrawString(row1, drawFont, drawBrush, , );
e.Graphics.DrawString(row2, drawFont, drawBrush, , );
e.Graphics.DrawString(row3, drawFont, drawBrush, , );
e.Graphics.DrawString(row4, drawFont, drawBrush, , );
e.Graphics.DrawString(row5, drawFont, drawBrush, , );
e.Graphics.DrawString(row6, drawFont, drawBrush, , ); //float pageW = 79f;
//float pageH = 50f;
//var docMargins = this.printDocument1.DefaultPageSettings.Margins;
//float recW = pageW - docMargins.Left - docMargins.Right;
//float recH = pageH - docMargins.Top - docMargins.Bottom;
//var rec1 = new RectangleF(0f, 0f, recW, recH);
//var rec2 = new RectangleF(0f, 0f, recW, recH);
//var rec3 = new RectangleF(0f, 0f, recW, recH);
//var rec4 = new RectangleF(0f, 0f, recW, recH);
//var rec5 = new RectangleF(0f, 0f, recW, recH);
//var rec6 = new RectangleF(0f, 0f, recW, recH); //e.Graphics.DrawString(row1, drawFont, drawBrush, rec1, sf);
//e.Graphics.DrawString(row2, drawFont, drawBrush, rec2, sf); //queuePrint.Dequeue();
//if (queuePrint.Count > 0)
//{
// e.HasMorePages = true;
//}
//else
//{
// e.HasMorePages = false;
//}
}
#endregion } public class TotalModel
{
/// <summary>
/// 商品编号
/// </summary>
public string ProNo { get; set; }
/// <summary>
/// 箱规
/// </summary>
public int PackageNum { get; set; }
}
public class PurchaseModel
{
public string DocNo { get; set; }
public string Destination { get; set; }
public string Warehouse { get; set; }
public string ProName { get; set; }
public int BoxNum { get; set; }
public int CurBoxNum { get; set; }
public string ProId { get; set; }
}
internal class Message
{
public int Count;
public PurchaseModel PM { get; set; }
}
/// <summary>
/// 队列
/// </summary>
/// <typeparam name="T"></typeparam>
internal class Queue<T> : List<T>
{
/// <summary>
/// 取出第一个元素
/// </summary>
/// <returns></returns>
public T Peek()
{
return this.FirstOrDefault();
}
/// <summary>
/// 删除第一个元素
/// </summary>
/// <returns></returns>
public T Dequeue()
{
var item = this.FirstOrDefault();
if (item != null)
{
Remove(item);
}
return item;
}
/// <summary>
/// 添加到末尾
/// </summary>
/// <param name="item"></param>
public void Enqueue(T item)
{
Add(item);
}
}
}