条形码、二维码标签
在生产环境中，标签应用也越来越广泛。引用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
4) 实施效果 含有条形码和二维码 图1：
图2：
5）记录踏过的坑
坑1：必须装office 2007 版本
坑2：需要安装PDF生成插件----SaveAsPDFandXPS
坑3：没有装code128.ttf字体，生成的二维码乱码
6) 完成每一次的Project，就是一次蜕变，越来越喜欢这种感觉。 在路上，继续前行！
