Multiple rows + columns with Excel template including images

Aspose.Total Product Family
1
Could you show example template + code, how to create following report with excel (or word):

[Barcode-image] [Barcode-image] [Barcode-image] [Barcode-image]
1234567890 1234567890 1234567890 1234567890

[Barcode-image] [Barcode-image] [Barcode-image] [Barcode-image]
1234567890 1234567890 1234567890 1234567890

: : : :

2

Hi,


I am a representative of Aspose.Cells team and would like to help regarding Excel files etc.

Well, you will require Aspose.BarCode for .NET product to generate barcodes for your needs and then paste them into the worksheet of Excel file by using Aspose.Cells APIs. See a sample code below for reference although I am not an expert of using APIs of Aspose.BarCode for .NET. I have also attached the output file for your reference.

Sample code:

//Instantiate BarCodeBuilder object
Aspose.BarCode.BarCodeBuilder bb = new Aspose.BarCode.BarCodeBuilder();

//Set the Code text for the barcode
bb.CodeText = “1234567890”;

//Set the symbology type to Code39Standard
bb.SymbologyType = Symbology.Code39Standard;

//Set the width of the bars to 1 millimeter
bb.xDimension = .1f;

//Creating memory stream
System.IO.MemoryStream ms = new System.IO.MemoryStream();

bb.BarCodeImage.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
Aspose.Cells.Drawing.RectangleShape shape = worksheet.Shapes.AddRectangle(0,1,4,3,100, 200);

shape.FillFormat.ImageData = ms.ToArray();

workbook.Save(“e:\test2\outputbarcodeimg.xlsx”);

Moreover, to help you more and give more details especially for Aspose.BarCode for .NET and Aspose.Words for .NET (you will need to use this product to paste barcode images in Word documents ), I am moving this thread to Aspose.Total forum where representatives of concerned teams will help you soon.

Thank you.


3

Thank you for your quick response.


Above sample shows, how to create excel-file with one barcode. Let me clarify little bit. What we need, is to create multi-row & multi-column pdf-report, using excel-template (or word-template) with smart markers, with barcode-images and barcode numbers. Barcode number is always below barcode image.
[Barcode-image] [Barcode-image] [Barcode-image] [Barcode-image]
1234567890 1234567890 1234567890 1234567890

[Barcode-image] [Barcode-image] [Barcode-image] [Barcode-image]
1234567890 1234567890 1234567890 1234567890

…


4

Hi Mloy,


I’m representative of Aspose.BarCode product family.

Aspose.BarCode for .NET allows developers to quickly and easily add barcode generation and recognition functionality to their Microsoft .NET applications. You can also repeat barcode generation and recognition process. Based on your query, you can set the location of a barcode image as above, below or none. For details, please visit the link below:
http://www.aspose.com/docs/display/barcodenet/Control+the+appearance+of+Code+text

Few helping links are there:
http://www.aspose.com/docs/display/barcodenet/Integration+with+Aspose+Components

I hope this will help you. Please let me know in case of further assistance and comments.

5

One more question. I tried to attach barcode images via excel template using image smart tags. Simplified code below (lines missing, so it’s so short as possible). Just to give the idea, how I’d like to use barcodes. Excel template contains several image smart tags, named table0.barcode1, etc. At this moment only results were text “memorystream” or “…tem.byte[]” instead of barcode images.


object barcode1;
object barcode2;
data.barcode1 = [imagedata]
data.barcode2 = [imagedata]
sheetData.Add(data);
wd.SetDataSource(“table” + sheet.Index.ToString(), sheetData);

Sample below (was behind your integration link) shows how to add image to excel document using rows and columns. It works fine. Could you show example where is used several different image smart markers with dynamic image names and image data is set via object data like above sheetData.

//Instantiate Excel class that represents an excel file

Workbook excel1 = new Workbook();

//Add the barcode image into the pictures collection of the first worksheet of

//the excel file in the form of a MemoryStream, ms with upper left row=5 and

//upper left column=5

excel1.Worksheets[0].Pictures.Add(5, 5, ms);

//Save the excel file

excel1.Save(“MyFile.xls”);

6

Hi,


To show pictures in smart marker tags, you need to use “picture” as a parameter in the smart markers. One important thing here is you have to get the image into byte array and needs to be saved into some container e.g datatable’s field etc. See the topic for your reference:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

Note: Please see the description and example under “Image Markers” sub-topic in the above document.

Thank you.

7

I have tested both ‘object’ and ‘byte array’ already. Both gave same results, only text shows in document, if I used “wd.SetDataSource(“table” + sheet.Index.ToString(), sheetData);”.


I have read and tested link you mentioned already and successfully get ‘image data’ into place of markers.

Problem is, if I create smart markers like the link shows, I have two different data sources, one with ‘image smart markers’ and one with ‘object data’ like ‘sheetData’ above. How to combine these? I need both data into same document. That’s why I’d like to get ‘image data’ inside ‘sheetData’ to make things as simple as possible. If it’s not possible to get ‘image data’ inside ‘sheetData’ and if it’s impossible to combine two data sources, one with ‘sheetData’ and one with ‘image data’, it’ll give me a lot of work to convert whole logic follow the table way.

8

Hi,


Well, as I understand you need to put two markers (image smart marker and data smart marker) in a single cell, I am afraid, this is not possible as you can only put one marker in a cell.

Thanks,
9

Hi,


Thanks for your inquiry and sorry for the delayed response. I am a representative of Aspose.Words team.

I would like to inform you that with Mail Merge feature of Aspose.Words, you can easily generate a look alike Word document report. I have attached a template Word document and the final report document here for your reference. I used the following code to generate the out.docx:

Document
doc = new Document(@“C:\Temp\Template.docx”);

doc.MailMerge.ExecuteWithRegions(GetDataTable());

doc.Save(@"C:\Temp\out.docx");

private static DataTable GetDataTable()

{

DataTable dataTable = new DataTable("report");

dataTable.Columns.Add(new DataColumn("bc1", typeof(string)));

dataTable.Columns.Add(new DataColumn("bc2", typeof(string)));

dataTable.Columns.Add(new DataColumn("bc3", typeof(string)));

dataTable.Columns.Add(new DataColumn("bc4", typeof(string)));

DataRow dataRow;

for (int i = 0; i < 10; i++)

{

dataRow = dataTable.NewRow();

dataRow["bc1"] = @"C:\Temp\Aspose logo.png";

dataRow["bc2"] = @"C:\Temp\Aspose logo.png";

dataRow["bc3"] = @"C:\Temp\Aspose logo.png";

dataRow["bc4"] = @"C:\Temp\Aspose logo.png";

dataTable.Rows.Add(dataRow);

}

return dataTable;

}


I would also like to suggest you please read the following articles to learn more about Aspose.Words' full fledge reporting feature:
http://www.aspose.com/docs/display/wordsnet/Prepare+a+Document
http://www.aspose.com/docs/display/wordsnet/How+to++Execute+Mail+Merge+with+Regions

Please let me know if I can be of any further assistance.

Best regards,
10

Thanks for your answers. I got this work other way. I have now document created by excel template with smart markers, where is several information with many barcodes.


I have one problem left. Size of barcodes is too big. I have tried to use several ways to adjust barcode size. Result is always that barcode readers won’t read them from pdf-print. Below is code, which works with barcode readers. On template is used setting scale100 for barcode images.

Barcode size is after print with this source code 7,8cm x 1,4cm. How do I get pdf-print, which works with barcode readers in size 2cm x 7mm or quite near of that?

CreateBarcodeImage(‘0001234001234560’); // Example

private byte[] CreateBarcodeImage(string codeText)
{
Worksheet barcodeSheet = new Workbook().Worksheets[0];
MemoryStream barcodeStream = new MemoryStream();
BarCodeBuilder barcodeBuilder = new BarCodeBuilder();
RectangleShape barcodeShape;
barcodeBuilder.SymbologyType = Symbology.Code39Standard;
barcodeBuilder.xDimension = .1f;
barcodeBuilder.CodeText = codeText;
barcodeBuilder.BarCodeImage.Save(
barcodeStream, System.Drawing.Imaging.ImageFormat.Bmp);
barcodeShape = barcodeSheet.Shapes.AddRectangle(0, 0, 0, 0, 100, 360);
barcodeShape.HasLine = false;
barcodeShape.FillFormat.ImageData = barcodeStream.ToArray();
return barcodeStream.ToArray();
}

11

You can forget question above. We decided to change barcode type, so the above problem is not relevant any more. We have everything clear now. Thanks for your quick answers :slight_smile:

12

Hi,


Thanks for your inquiry and sorry for delayed response. I would like to update you that you can also generate the barcode using custom width support feature as described here.
http://www.aspose.com/docs/display/barcodenet/Generate+Barcode+Using+Custom+Width+Support

I hope this will help you in the future. Please let me know in case of further assistance and comments.