.net下，读取Excel数据和使用Bartender打印标签-526互联

背景：bartender 使用txt或Excel作为数据源打印标签时，有时候需要对数据源中的原生数据执行复杂的逻辑变化。例如，可能需要从Excel中读取某一行，然后根据该行的某个值打印相同数量的标签，每个标签上的流水号都不同。

实现：使用.net BTSDK实现。

效果如图所示：

准备：

BT：安装bartender10.1（勾选SDK，程序中调用）

Excel文档工具库：NPOI.2.1.3.1\lib\net20

Winform：.NET framework 3.0 x86

关键代码：

1、读取Excel数据

public List<record> ReadFromExcelFile(string filePath)
{
    IWorkbook wk = null;
    string extension = Path.GetExtension(filePath);
    try
    {
        FileStream fs = File.OpenRead(filePath);
        if (extension.Equals(".xls"))
        {
            wk = new HSSFWorkbook(fs);
        }
        if (extension.Equals(".xlsx"))
        {
            wk = new XSSFWorkbook(fs);
        }
        fs.Close();
        List<record> list = new List<record>();

        //读取当前表
        ISheet sheet = wk.GetSheetAt(0);
        IRow row = sheet.GetRow(1);
        for (int i = 1; i <= sheet.LastRowNum; i++)
        {
            row = sheet.GetRow(i);
            //过滤掉不需要打印的行
            if (null == row.GetCell(6) || row.GetCell(6).ToString() == "N" || row.GetCell(6).ToString() == "n" || row.GetCell(6).ToString().Length==0)
                continue;
            //将需要打印的数据添加到list中
            if (row != null)
            {
                list.Add(new record()
                {
                    SO = (null == row.GetCell(0)) ?"":row.GetCell(0).ToString(),
                    PO = (null == row.GetCell(1)) ? "" : row.GetCell(1).ToString(),
                    WLID = (null == row.GetCell(2)) ? "" : row.GetCell(2).ToString(),
                    KHWLID = (null == row.GetCell(3)) ? "" : row.GetCell(3).ToString(),
                    DESC = (null == row.GetCell(4)) ? "" : row.GetCell(4).ToString(),
                    COUNT = (null == row.GetCell(5)) ? "" : row.GetCell(5).ToString(),
                    FLAG = row.GetCell(6).ToString()
                });
            }
        }
        return list;
    }
    catch (Exception ex)
    {

        throw ex;
    }
}

View Code

2、生产序列号

public List<string> createSN(int num,int len)
{
    if (num.ToString().Length > len)
        return null;

    string year = DateTime.Now.ToString("yyyy");
    string month = DateTime.Now.ToString("MM");
    string day = DateTime.Now.ToString("dd");
    string y = Enum.GetName(typeof(Year), int.Parse(year));
    string m = null!= Enum.GetName(typeof(Year), int.Parse(month))?Enum.GetName(typeof(Year), int.Parse(month)): int.Parse(DateTime.Now.ToString("MM")).ToString();
    string d = null!= Enum.GetName(typeof(Year), int.Parse(day))? Enum.GetName(typeof(Year), int.Parse(day)): int.Parse(DateTime.Now.ToString("dd")).ToString();
    string SN = y + m + d;
    List<string> ls = new List<string>();
    int i = 1;
    while (i <= num)
    {
        ls.Add(SN + i.ToString().PadLeft(len, '0'));
        ++i;
    }
    return ls;
}

View Code

3、根据“订货数量”扩展标签数量

public List<record> Generate(List<record> ls, int len)
{
    SNService snServiceObj = new SNService();
    List<record> list = new List<record>();
    List<string> strList = null;
    foreach (var reItem in ls)
    {
        strList = snServiceObj.createSN(int.Parse(reItem.COUNT), len);
        foreach (var item in strList)
        {
            list.Add(new record()
            {
                SO = reItem.SO,
                SN = item,
                PO = reItem.PO,
                WLID = reItem.WLID,
                KHWLID = reItem.KHWLID,
                DESC = reItem.DESC,
                COUNT = reItem.COUNT
            });
        }
    }
    return list;
}

View Code

4、调用BTSDK，打印标签

private void btnPrinter_Click(object sender, EventArgs e)
{
    //提取Excel数据
    ExcelService excelServiceObj = new ExcelService();
    List<record> list = excelServiceObj.ReadFromExcelFile("mo.xlsx");
    List<record> ls = Generate(list, 4);
    string btwPath = Directory.GetCurrentDirectory()+ "\\Barcode.btw";
    KillBT();
    Thread.Sleep(100);
    //创建bartender引擎
    Engine engine = new Engine();
    try
    {
        // 打开模板
        engine.Start();
        // 替换成你的模板路径
        LabelFormatDocument labelFormat = engine.Documents.Open(btwPath); 
        labelFormat.PrintSetup.PrinterName = this.cboPrinter.SelectedItem.ToString();
        labelFormat.PrintSetup.IdenticalCopiesOfLabel = 1;//设置打印张数
        labelFormat.PrintSetup.NumberOfSerializedLabels = 1;//序列化标签数
        Result result = 0;
        // 设置模板中的数据,替换成你的实际数据
        foreach (var item in ls)
        {
            labelFormat.SubStrings["SO"].Value = item.SO;
            labelFormat.SubStrings["PO"].Value = item.PO;
            labelFormat.SubStrings["DESC"].Value = item.DESC;
            labelFormat.SubStrings["SN"].Value = item.KHWLID + " " + item.PO + " " + item.SN;
            labelFormat.SubStrings["DATE"].Value = DateTime.Today.ToShortDateString();
            result = labelFormat.Print("printerC"); // 替换成你的文档名称
        }


        // 打印
        //labelFormat.PrintSetup.PrinterName = printer.PrinterName;
        //labelFormat.PrintSetup.PrinterName = this.cboPrinter.SelectedItem.ToString();
        //labelFormat.PrintSetup.IdenticalCopiesOfLabel = 1;//设置打印张数
        //labelFormat.PrintSetup.NumberOfSerializedLabels = 1;//序列化标签数
        //Result result = labelFormat.Print("printerC"); // 替换成你的文档名称

        // 关闭模板和BarTender引擎
        labelFormat.Close(SaveOptions.DoNotSaveChanges);
        engine.Stop();

    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}