条形码、二维码标签

时间 2021-01-03
在生产环境中，标签应用也越来越广泛。引用Microsoft.Office.Interop.Excel.dll程序集来实现此功能。
1）解决方案
2）程序主界面
3）程序实现功能：Text文档导入数据库，Excel文档导入数据库，在数据库中生成标签数据---->填充到Excel模板------>Sheet排版---->生成PDF
     string str5;
                    string str11;
                    #region 开始填充数据
                    workSheet2.get_Range("A4", Missing.Value).set_Value(Missing.Value, dt.Rows[j][7].ToString());
                    workSheet2.get_Range("O2", Missing.Value).set_Value(Missing.Value, "");
                    if (Conversions.ToDouble(Conversions.ToString(Strings.Len(dt.Rows[j][2].ToString()))) <= 8)
                    {
                        str5 = Strings.Mid(dt.Rows[j][2].ToString(), 1, 4) + "-" + Strings.Mid(dt.Rows[j][2].ToString(), 5, Strings.Len(dt.Rows[j][2].ToString()) - 4);
                    }
                    else
                    {
                        str5 = Strings.Mid(dt.Rows[j][2].ToString(), 1, 4) + "-" + Strings.Mid(dt.Rows[j][2].ToString(), 5, 4) + "-" + Strings.Mid(dt.Rows[j][2].ToString(), 9, Strings.Len(dt.Rows[j][2].ToString()) - 8);
                    }
                    workSheet2.get_Range("A9", Missing.Value).set_Value(Missing.Value, str5);
                    workSheet2.get_Range("A11", Missing.Value).set_Value(Missing.Value, Code128B(dt.Rows[j][2].ToString()));
                    workSheet2.get_Range("Q9", Missing.Value).set_Value(Missing.Value, RuntimeHelpers.GetObjectValue(dt.Rows[j][3]));
                    workSheet2.get_Range("V9", Missing.Value).set_Value(Missing.Value, dt.Rows[j][5].ToString());
                    workSheet2.get_Range("A18", Missing.Value).set_Value(Missing.Value, dt.Rows[j][11].ToString());
                    workSheet2.get_Range("C14", Missing.Value).set_Value(Missing.Value, dt.Rows[j][0x10].ToString());
                    workSheet2.get_Range("Q14", Missing.Value).set_Value(Missing.Value, RuntimeHelpers.GetObjectValue(dt.Rows[j][12].ToString()));
                    workSheet2.get_Range("S11", Missing.Value).set_Value(Missing.Value, "'" + dt.Rows[j][8].ToString() + "/" + dt.Rows[j][9].ToString());
                    workSheet2.get_Range("N18", Missing.Value).set_Value(Missing.Value, Strings.Format(Conversions.ToDate(dt.Rows[j][10].ToString()), "yyyyMMdd"));
                    workSheet2.get_Range("I4", Missing.Value).set_Value(Missing.Value, dt.Rows[j][6].ToString());
                    if (cmbStr == "")
                    {
                        string temp = dt.Rows[j][0].ToString() + str2 + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "0000");
                        workSheet2.get_Range("A20", Missing.Value).set_Value(Missing.Value, Code128B(temp));
                        workSheet2.get_Range("A22", Missing.Value).set_Value(Missing.Value, temp);
                    }
                    else
                    {
                        workSheet2.get_Range("A20", Missing.Value).set_Value(Missing.Value, Code128B(dt.Rows[j][0].ToString() + str2 + cmbStr + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "000")));
                        workSheet2.get_Range("A22", Missing.Value).set_Value(Missing.Value, dt.Rows[j][0].ToString() + str2 + cmbStr + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "000"));
                    }

                    //整数箱
                    if (string.Compare(dt.Rows[j][4].ToString(), "0") == 0)
                    {
                        workSheet2.get_Range("A23", Missing.Value).set_Value(Missing.Value, "Dongguan Aikawa Teiron Electronics Co.,Ltd.");
                        workSheet2.get_Range("A23", Missing.Value).Interior.ColorIndex = 0;
                    }
                    else
                    {
                        //尾箱
                        workSheet2.get_Range("A23", Missing.Value).set_Value(Missing.Value, "Dongguan Aikawa Teiron Electronics Co.,Ltd.(Remainder)");
                        workSheet2.get_Range("A23", Missing.Value).Interior.ColorIndex = 3;
                    }
                    #endregion
                    //2018-03-16 将填充好的数据复制到Tmp页面中 
                    workSheet2.get_Range("A1:W23", Missing.Value).Copy(Missing.Value);
                    #region 页面布局
                    if ((i % 2) == 0)//在“tmp”页面中，从第2列开始
                    {
                        workSheet.get_Range("Z" + Conversions.ToString((int)(((num2 - 1) * num4) + 1)), Missing.Value).PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                    }
                    else//奇数时从第1列开始
                    {
                        workSheet.get_Range("A" + Conversions.ToString((int)(((num2 - 1) * num4) + 1)), Missing.Value).PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                    }
                    #endregion
                    #region 格式调整
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 1, Missing.Value], null, "RowHeight", new object[] { 5.25 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[Conversions.ToString((int)(((i - 1) * num4) + 1)) + ":" + Conversions.ToString((int)(i * 0x1c)), Missing.Value], null, "RowHeight", new object[] { 10.5 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 1, Missing.Value], null, "RowHeight", new object[] { 8.25 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["A:AW", Missing.Value], null, "ColumnWidth", new object[] { 1.38 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["H", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["M", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["P", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * 0x1b) + 14, Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * 0x1b) + 15, Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);

                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 2, Missing.Value], null, "RowHeight", new object[] { 5.25 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 3, Missing.Value], null, "RowHeight", new object[] { 5.25 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 4, Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 5, Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 6, Missing.Value], null, "RowHeight", new object[] { 12 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 7, Missing.Value], null, "RowHeight", new object[] { 7.5 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 13, Missing.Value], null, "RowHeight", new object[] { 7.5 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Rows[((i - 1) * num4) + 0x10, Missing.Value], null, "RowHeight", new object[] { 7.5 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["Y", Missing.Value], null, "ColumnWidth", new object[] { 8.38 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["AG", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["AL", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);
                    NewLateBinding.LateSetComplex(workSheet.Columns["AO", Missing.Value], null, "ColumnWidth", new object[] { 0.62 }, null, null, false, true);

                    #endregion
                    #region 生成二维码  20171109
                    if (cmbStr == "")
                    {
                        str11 = dt.Rows[j][0].ToString() + str2 + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "0000");
                    }
                    else
                    {
                        str11 = dt.Rows[j][0].ToString() + str2 + cmbStr + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "000");
                    }
                    string str6 = GetString(dt.Rows[j][7].ToString(), 10);
                    string str8 = GetString(dt.Rows[j][2].ToString(), 0x19);
                    string str9 = GetString(dt.Rows[j][5].ToString(), 5);
                    string str10 = GetString(dt.Rows[j][6].ToString(), 10);
                    string str4 = GetString(dt.Rows[j][0x10].ToString(), 20);
                    string str13 = str11 + str6 + str8 + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][3]), "000000000") + str9 + dt.Rows[j][11].ToString() + str10 + str4 + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][12]), "000000000") + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][8]), "00000") + Strings.Format(RuntimeHelpers.GetObjectValue(dt.Rows[j][9]), "00000") + Strings.Format(Conversions.ToDate(dt.Rows[j][10]), "yyyyMMdd");
                    //二维码
                    string expression = CrtQrCodeWithZXing(str13, i);

                    if (Strings.Len(expression) > 0)
                    {
                        Excel.Range range;
                        ((Excel._Worksheet)workSheet).Activate();

                        if ((i % 2) == 0)
                        {
                            //0x2b --->43
                            range = (Excel.Range)workSheet.Cells[((num2 - 1) * num4) + 3, 0x2b];
                            num2++;
                        }
                        else
                        {
                            //0x12--->18
                            range = (Excel.Range)workSheet.Cells[((num2 - 1) * num4) + 3, 0x12];
                        }

                        Excel.Shape shape5 = workSheet.Shapes.AddPicture(expression, MsoTriState.msoTrue, MsoTriState.msoTrue, 200f, 100f, 80f, 80f);
                        shape5.Top = Conversions.ToSingle(Operators.SubtractObject(range.Top, 3.5));
                        shape5.Left = Conversions.ToSingle(Operators.AddObject(range.Left, 7));
                        shape5.Height = Conversions.ToSingle(Operators.MultiplyObject(range.Height, 8.3));
                        shape5.Width = Conversions.ToSingle(Operators.MultiplyObject(range.Height, 8.3));
                        shape5 = null;
                    }
                    #endregion
                    #region 换页控制
                    if ((i % 6) == 0)
                    {
                        NewLateBinding.LateSetComplex(workSheet.Rows[((num2 - 1) * num4) + 1, Missing.Value], null, "pagebreak", new object[] { 1 }, null, null, false, true);
                    }
                    #endregion
View Code
4) 实施效果含有条形码和二维码图1：
图2：
5）记录踏过的坑
坑1：必须装office 2007 版本
坑2：需要安装PDF生成插件----SaveAsPDFandXPS
坑3：没有装code128.ttf字体，生成的二维码乱码
6) 完成每一次的Project，就是一次蜕变，越来越喜欢这种感觉。在路上，继续前行！