Create pdf file from MySql table containing html code using iText7 and ASP NET C#

Question

User-1453200658 posted

Hi,

This is table t_contents_2021 on database MySql version 8.0.17

-- ----------------------------
-- Table structure for t_contents_2021
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_2021`;
CREATE TABLE `t_contents_2021`  (
  `contents` varchar(1000)  DEFAULT NULL,
  `sUnity` varchar(255)  DEFAULT NULL,
  `sID_content_image` varchar(255)  DEFAULT NULL,
  `sID_content_image_link` varchar(255)  DEFAULT NULL,
  `sID_content_ppt` varchar(255)  DEFAULT NULL,
  `sID_content_ppt_link` varchar(255)  DEFAULT NULL,
  `sID_content_xls` varchar(255)  DEFAULT NULL,
  `sID_content_xls_link` varchar(255)  DEFAULT NULL,
  `sID_content_pdf` varchar(255)  DEFAULT NULL,
  `sID_content_pdf_link` varchar(255)  DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `contents`(`contents`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents_2021
-- ----------------------------
INSERT INTO `t_contents_2021` VALUES ('Set 1', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `t_contents_2021` VALUES ('- Par 1.1', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2);
INSERT INTO `t_contents_2021` VALUES ('<b>bold text</b>', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3);
INSERT INTO `t_contents_2021` VALUES ('- Par 1.2', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4);
INSERT INTO `t_contents_2021` VALUES ('normal text', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 5);
INSERT INTO `t_contents_2021` VALUES ('Set 2', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 6);
INSERT INTO `t_contents_2021` VALUES ('- Par 2.1', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 7);
INSERT INTO `t_contents_2021` VALUES ('<i>italic text</i>', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 8);
INSERT INTO `t_contents_2021` VALUES ('- Par 2.2', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9);
INSERT INTO `t_contents_2021` VALUES ('<u>underline text</u>', 'Q400', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 10);

On the table t_contents_2021 in field contents there is some html code

<b>bold text</b>
<i>italic text</i>
<u>underline text</u>

Now I use iText (Version 7.1.13) to convert html to pdf using HtmlConverter (html2pdf version 2.0.1)

string filename;
string html;
string dest;

Paragraph contents;

protected void Page_Load(object sender, EventArgs e)
{
    PdfWriter writer = new PdfWriter(Response.OutputStream);
    PdfDocument pdf = new PdfDocument(writer);
    Document document = new Document(pdf);

    using (MySqlConnection cn =
        new MySqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {

        using (MySqlCommand cmd =
            new MySqlCommand("SP", cn))
        {
            cmd.Connection.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("tunity", "Q400");

            MySqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    contents = new Paragraph(reader.GetString("contents"))
                    .SetTextAlignment(TextAlignment.JUSTIFIED)
                    .SetFontSize(12);

                    if (reader.GetString("contents").StartsWith("Set"))
                    {
                        contents.SetFontSize(12)
                            .SetBold()
                            .SetFontColor(ColorConstants.BLUE);
                        document.Add(newline);
                    }
                    else if (reader.GetString("contents").StartsWith("- "))
                    {
                        contents.SetFontSize(10)
                            .SetBold()
                            .SetFontColor(ColorConstants.BLACK);
                        document.Add(newline);
                    }
                    else
                    {
                        contents.SetFontSize(10)
                            .SetFontColor(ColorConstants.BLACK);
                    }

                    if (!String.IsNullOrEmpty(reader["sID_content_image"].ToString()))
                    {
                        Image image_path = new Image(ImageDataFactory
                              .Create(reader["sID_content_image"].ToString()))
                              .SetTextAlignment(TextAlignment.CENTER);

                        image_path.SetAutoScale(false);
                        image_path.SetBorderLeft(new SolidBorder(ColorConstants.DARK_GRAY, 2));
                        image_path.SetBorderRight(new SolidBorder(ColorConstants.DARK_GRAY, 2));
                        image_path.SetBorderTop(new SolidBorder(ColorConstants.DARK_GRAY, 2));
                        image_path.SetBorderBottom(new SolidBorder(ColorConstants.DARK_GRAY, 2));
                        document.Add(image_path);
                    }

                    if (!String.IsNullOrEmpty(reader["sID_content_ppt"].ToString()))
                    {
                        string IMG1 = "C:\\inetpub\\wwwroot\\aspnet\\img\\powerpoint-png-picture-2.jpg";
                        Image img1 = new Image(ImageDataFactory.Create(IMG1));
                        Table tablelink = new Table(UnitValue.CreatePercentArray(1)).UseAllAvailableWidth();
                        PdfFont font = PdfFontFactory.CreateFont(StandardFonts.HELVETICA_BOLD);

                        Cell cell = new Cell().Add(img1.SetAutoScale(false));
                        cell.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        img1.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        cell.SetBorder(Border.NO_BORDER);

                        cell.SetAction(PdfAction.CreateURI(
                            reader["sID_content_ppt_link"].ToString()));
                        tablelink.AddCell(cell);
                        document.Add(tablelink);
                        document.Add(newline);
                    }

                    if (!String.IsNullOrEmpty(reader["sID_content_xls"].ToString()))
                    {
                        string IMG1 = "C:\\inetpub\\wwwroot\\img\\excel-2.png";
                        Image img1 = new Image(ImageDataFactory.Create(IMG1));
                        Table tablelink = new Table(UnitValue.CreatePercentArray(1)).UseAllAvailableWidth();
                        PdfFont font = PdfFontFactory.CreateFont(StandardFonts.HELVETICA_BOLD);
                        Cell cell = new Cell().Add(img1.SetAutoScale(false));
                        cell.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        img1.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        cell.SetBorder(Border.NO_BORDER);
                        cell.SetAction(PdfAction.CreateURI(
                            reader["sID_content_xls_link"].ToString()));
                        tablelink.AddCell(cell);
                        document.Add(tablelink);
                        document.Add(newline);
                    }

                    if (!String.IsNullOrEmpty(reader["sID_content_pdf"].ToString()))
                    {
                        string IMG1 = "C:\\inetpub\\wwwroot\\img\\pdf-png-picture-2.gif";
                        Image img1 = new Image(ImageDataFactory.Create(IMG1));
                        Table tablelink = new Table(UnitValue.CreatePercentArray(1)).UseAllAvailableWidth();
                        PdfFont font = PdfFontFactory.CreateFont(StandardFonts.HELVETICA_BOLD);
                        Cell cell = new Cell().Add(img1.SetAutoScale(false));
                        cell.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        img1.SetHorizontalAlignment(HorizontalAlignment.CENTER);
                        cell.SetBorder(Border.NO_BORDER);
                        cell.SetAction(PdfAction.CreateURI(
                            reader["sID_content_pdf_link"].ToString()));
                        tablelink.AddCell(cell);
                        document.Add(tablelink);
                        document.Add(newline);
                    }

                    document.Add(contents);
                }

                filename = @"C:\\inetpub\\wwwroot\\public\\Management_" + Guid.NewGuid() + ".pdf";
                html = contents.ToString();
                dest = filename.ToString();
                HtmlConverter.ConvertToPdf(html, new FileStream(dest, FileMode.Create));
            }
            else
            {
                Console.WriteLine("No rows found.");
            }

            reader.Close();
            cmd.Connection.Close();
        }
    }

    document.Close();

    Response.Clear();
    Response.ContentType = "application/pdf";
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + dest);
    Response.TransmitFile(dest);
    Response.End();
}

The generated pdf file Management_14a40257-547f-4b6f-b991-eaa4aeb47a9a.pdf doesn't contains the values from table t_contents_2021 but contains only this text:

Help me to do it

This is the Stored procedure in MySql

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP`(IN tunity CHAR(100))
BEGIN

SET @s = CONCAT('SELECT * FROM t_contents_2021 WHERE sUnity = \'',tunity,'\';');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE `stmt`;

END

Answer 1

User-1716253493 posted

AFAIK, the codes should add newline, html etc into paragraph (contents) then finaly add contents into documents

But your code adding empty paragraph into document

Ensure if condition is match.

Read this

https://api.itextpdf.com/iText7/dotnet/7.1.8/classi_text_1_1_layout_1_1_element_1_1_paragraph.html

Answer 2

User-1453200658 posted

thanks for help.

If not use on my code the HtmlConverter.ConvertToPdf

                filename = @"C:\\inetpub\\wwwroot\\public\\Management_" + Guid.NewGuid() + ".pdf";
                html = contents.ToString();
                dest = filename.ToString();
                HtmlConverter.ConvertToPdf(html, new FileStream(dest, FileMode.Create));
            

the pdf file is created correctly but on pdf I have

Answer 3

User-1716253493 posted

Try read this

https://stackoverflow.com/questions/6623963/html-tag-not-accepted-in-itextsharp-and-text-out-of-borders

Seem because you add html tag into paragraph

Answer 4

User-1453200658 posted

On your link using itextsharp library and not itext7 library...