C# .NET - Problem with Excel conversion to PDF

Asked By mostafa hamdy on 22-May-11 05:45 AM
Hello all
I have some application in C# convert from the MS Excel sheet to PDFusing the

Microsoft.Office.Interop.Excel

 namespace and the code is as the following:

object paramMissing = Type.Missing;

XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;

XlFixedFormatQuality paramExportQuality = XlFixedFormatQuality.xlQualityStandard;

bool paramOpenAfterPublish = true;

bool paramIncludeDocProps = true;

bool paramIgnorePrintAreas = true;

object paramFromPage = Type.Missing;

object paramToPage = Type.Missing;

// Open the source workbook.

excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath.ToString(), paramMissing, paramMissing, paramMissing,

paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,

paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);

// Save it in the target format.

if (excelWorkBook != null)

excelWorkBook.ExportAsFixedFormat(paramExportFormat, paramExportFilePath, paramExportQuality, paramIncludeDocProps,

paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish, paramMissing);

the problem is that after conversion from XLS or XLSX files to PDF format , th resulting files in PDF is not correct I mean that if there's one  sheet in the XLS file it's splitted to about 4 or 5 pages in the PDF every 3 or 4 columns in the xls sheet is converted to a separated page in PDF , I just want it to becomes only page in the pdf , please if any body get what I mean and can help me
please send me or tell me about some url may help me in doing that
regards
Mostafa

Nikhil Mahajan replied to mostafa hamdy on 22-May-11 08:29 AM
There are two processes to achieve it. You can retrieve the data from excel file and import into DataSet. And then you can use ABCpdf to create a new pdf file with the datas from DataSet.

1. To retrieve the data from excel file and import into DataSet use below code 

To achieve this, you can use OLE flexiblly. On the other hand, you can also use Microsoft.Office.Interop.Excel. There is a sample as below you can try.

using Microsoft.Office.Interop.Excel;
using System.Text;
using System.Reflection;


public DataSet GetExcel(string fileName)
  {
    Application oXL;
    Workbook oWB;
    Worksheet oSheet;
    Range oRng;
    try
    {
      //  creat a Application object
      oXL = new ApplicationClass();
      //   get   WorkBook  object
      oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
          Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
          Missing.Value, Missing.Value);
 
      //   get   WorkSheet object
      oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
      System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
      DataSet ds = new DataSet();
      ds.Tables.Add(dt);
      DataRow dr;
 
      StringBuilder sb = new StringBuilder();
      int jValue = oSheet.UsedRange.Cells.Columns.Count;
      int iValue = oSheet.UsedRange.Cells.Rows.Count;
      //  get data columns
      for (int j = 1; j <= jValue; j++)
      {
        dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
      }
 
      //string colString = sb.ToString().Trim();
      //string[] colArray = colString.Split(':');
 
      //  get data in cell
      for (int i = 1; i <= iValue; i++)
      {
        dr = ds.Tables["dtExcel"].NewRow();
        for (int j = 1; j <= jValue; j++)
        {
          oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
          string strValue = oRng.Text.ToString();
          dr["column" + j] = strValue;
        }
        ds.Tables["dtExcel"].Rows.Add(dr);
      }
      return ds;
    }
    catch (Exception ex)
    {
      Label1.Text = "Error: ";
      Label1.Text += ex.Message.ToString();
      return null;
    }
    finally
    {
      Dispose();
    }

 

2. And then you can use  ABCpdf to create a pdf file with the data from DataSet.

You can try this Three-Party Control  to operate and read/create your PDF file. There is a link to download: http://www.websupergoo.com/download.htm

There is a great demo available in the above link and you can learn how to use it.

Support document: http://www.websupergoo.com/support.htm 

Feature chart: http://www.websupergoo.com/abcpdf-8.htm

OR 
You can use below code to create pdf file :
WebSupergoo.ABCpdf6.Doc theDoc = new WebSupergoo.ABCpdf6.Doc();
     
    theDoc.Rect.Inset(50, 90);
    theDoc.Page = theDoc.AddPage();
    int theID;
    theID = theDoc.AddImageUrl(System.Web.HttpContext.Current.Request.Url.ToString());
 
    while (true)
    {
      theDoc.FrameRect(); // add a black border
      if (!theDoc.Chainable(theID))
        break;
      theDoc.Page = theDoc.AddPage();
      theID = theDoc.AddImageToChain(theID);
    }
 
    for (int i = 1; i <= theDoc.PageCount; i++)
    {
      theDoc.PageNumber = i;
      theDoc.Flatten();
    }
 
    if (Request.QueryString["DocumentNo"] != null)
    {
      string dateNow = "(" + DateTime.Now.ToString().Replace("/", "-") + ")";
      dateNow = dateNow.Replace(" ", "-");
      dateNow = dateNow.Replace(":", "-");
      theDoc.Save(Server.MapPath("PDFInvoices/" + Request.QueryString["DocumentNo"].ToString() + dateNow + ".pdf"));
    }
     
    theDoc.Clear();


Hope this can help.
TSN ... replied to mostafa hamdy on 22-May-11 11:45 PM
hi..

you need to use the conversion third party tool in oprder to have your task..

i am showing the example with the dotcretor..


) Add a reference in your project to docCreator library.
To do this:
    a. On the Project menu, click Add Reference.
    b. On the COM tab, locate Neevia docCreator and then click Select.
    c. Click OK in the Add References dialog box to accept your selections.

2) Add a reference in your project to Microsoft Excel.
To do this:
    a. On the Project menu, click Add Reference;
    b. On the COM tab, locate Microsoft Excel and then click Select;
    c. Click OK in the Add References dialog box to accept your selections.

3) Configure MS Excel like recommended below:
  • type dcomcnfg in the command prompt and press Enter;
  • find and select Microsoft Excel Application in the Applications list, then press the Properties button;
    Note: If you have Windows 2003\2008 then type dcomcnfg in the command prompt, expand the Component Services group, expand the Computers group, expand the My Computer group, expand the DCOM Config group, find and select the Microsoft Excel Application->right mouse click->Properties.
  • click the Identity tab. Check the "This user" checkbox, press Browse and specify the Administrator account;
  • enter and re-enter the Administrator password;
  • click the Security tab. Check the "Use custom access permissions" checkbox, press Edit and add the ASPNET, IUSR_ and IWAM_ user accounts;
    Note: If you have Windows 2003\2008 also add the "NETWORK SERVICE" user account;
  • check the "Use custom launch permissions" checkbox, press Edit and add the ASPNET, IUSR_ and IWAM_ user accounts;
    Note: If you have Windows 2003\2008 also add the "NETWORK SERVICE" user account;
  • reboot the computer;
  • http://www.neevia.com/support/examples/cr/?get=ex003aspnet