GemBox for Excel

snip_GemBoxLogoA few weeks back I had a requirement to have a web page link that when clicked, the server side postback would open an excel file (as a template with formulas and such), populate a few fields in the worksheet and copy row 37 and for each record in result set, insert/past that copied row and fill in fill in data from my result set.  It may sound complicated, but not that much.  Thankfully Microsoft Excel exposes some components to do such thing quite easily.

BUT… Office Excel needs to be available (installed) on the web server.  THIS, generally speaking by most network security folks is a bad thing.  Enter 3rd party components such as those offered by GemBox Software.  GemBox offers components for your code to easily work with documents (WORD .doc and .docx, HTL, XPS, RTF, snip_GemBoxSpreadsheetTXT), presentations (PowerPoint .ppt and .pptx, XPS and image formats), emails (MSG, EML, MHTML and MBOX via POP, IMAP and SMTP) PDF files and… Spreadsheets (Excel .XLSX, .XLS, CSV, HTML)

So, after including two small DLL’s in your project, with easy to use methods to open, manipulate and save the excel file… the task became quite simple with no concerns with deployment and no Office installed on the server.

Here is my sample code:

    string fName = null;
     fName = Server.MapPath(".") + "\\Reports\\China_Template.xlsx";
     ExcelFile excelFile = ExcelFile.Load(fName);

    // Get the first workseet in the file
     ExcelWorksheet excelWorksheet = excelFile.Worksheets[0];

    // Find cells with placeholder text and set their values
     int row, column;
     if (excelWorksheet.Cells.FindText("Acct", true, true, out row, out column))
     {
         excelWorksheet.Cells[row, column].Value = ds.Tables[0].Rows[0]["Cust"].ToString();
     }
     if (excelWorksheet.Cells.FindText("AcctName", true, true, out row, out column))
     {
         excelWorksheet.Cells[row, column].Value = ds.Tables[0].Rows[0]["CustName"].ToString();
     }
     if (excelWorksheet.Cells.FindText("SalesRep", true, true, out row, out column))
     {
         excelWorksheet.Cells[row, column].Value = ds.Tables[0].Rows[0]["RepName"].ToString();
     }

    // Copy the template row for the number of records we found in the order
     int templateRow = 36;  // zero based rows...
     excelWorksheet.Rows.InsertCopy(templateRow + 1, Recs, excelWorksheet.Rows[templateRow]);

    // set columns for Images and SKU
     string imageCol = "B";

    // Fill inserted rows with order items
     int i = templateRow;
     foreach (DataRow dataRow in ds.Tables[0].Rows)
     {
         // Add Order item details to the worksheet
         ExcelRow currentRow = excelWorksheet.Rows[i];
         currentRow.Cells["A"].SetValue(port);
         currentRow.Cells["C"].SetValue(dataRow["ItemNbr"].ToString());
         currentRow.Cells["D"].SetValue(dataRow["ItemDesc"].ToString());
         currentRow.Cells["E"].SetValue(dataRow["AvailQty"].ToString());
         currentRow.Cells["F"].SetValue(Convert.ToInt16(dataRow["Qty"].ToString()));
         currentRow.Cells["G"].SetValue(dataRow["Amount"].ToString());
         currentRow.Cells["I"].SetValue(dataRow["Cubes"].ToString());

        // set imageRootPath
         string imageName = "sn_" + dataRow["ItemNbr"].ToString() + "_LR.jpg";
         string imagePathName = string.Empty;
         if (ConfigurationManager.AppSettings["WebEnvironment"] == "Server")
         {
             if (File.Exists(ConfigurationManager.AppSettings["ProductImageRoot"] + "\\" + imageName))
             {
                 imagePathName = ConfigurationManager.AppSettings["ProductImageRoot"] + "\\" + imageName;
             }
             else if (File.Exists(ConfigurationManager.AppSettings["ProductLineArtRoot"] + "\\" + imageName))
             {
                 imagePathName = ConfigurationManager.AppSettings["ProductLineArtRoot"] + "\\" + imageName;
             }
             else
             {
                 imagePathName = Server.MapPath(".") + "\\Images\\no_image.png";
             }
         }
         else
         {
             if (File.Exists(ConfigurationManager.AppSettings["ProductImageRootLocal"] + "\\" + imageName))
             {
                 imagePathName = ConfigurationManager.AppSettings["ProductImageRootLocal"] + "\\" + imageName;
             }
             else if (File.Exists(ConfigurationManager.AppSettings["ProductLineArtRootLocal"] + "\\" + imageName))
             {
                 imagePathName = ConfigurationManager.AppSettings["ProductLineArtRootLocal"] + "\\" + imageName;
             }
             else
             {
                 imagePathName = Server.MapPath(".") + "\\Images\\no_image.png";
             }
         }

         // Add product picture to the worksheet
         // i + 1 for adding pictures, ... well... because it is different.
         // This TRY may not be needed, but when testing at home, the images were NOT accessable and it crashed.
         ExcelPicture picture;
         try
         {
             picture = excelWorksheet.Pictures.Add(imagePathName, imageCol + (i + 1).ToString());
         }
         catch (Exception)
         {
             picture = excelWorksheet.Pictures.Add("/Images/no_image.png", imageCol + (i + 1).ToString());
         }

        picture.Position.Mode = PositioningMode.Move;
         //excelWorksheet.Columns[imageCol].AutoFit();

        // Get original picture size and picture column width
         double pictureWidth = picture.Position.Width;
         double pictureHeight = picture.Position.Height;
         double columnWidth = excelWorksheet.Columns[imageCol].GetWidth(LengthUnit.Point);

        // Resize the picture with a cell margin of 10
         double margin = 10;
         picture.Position.Width = (columnWidth - margin);
         double pictureChangeFactor = picture.Position.Width / pictureWidth;
         picture.Position.Height = pictureHeight * pictureChangeFactor;
         // Adjust the height of the row based on the height of the picture and margin
         currentRow.SetHeight(picture.Position.Height + margin, LengthUnit.Point);
         picture.Position.Left = picture.Position.Left + (margin / 2);
         picture.Position.Top = picture.Position.Top + (margin / 2);

        // increment i to the next row in the excel worksheet
         i++;
     }

    // Run calculations on the worksheet
     excelWorksheet.Calculate();

    string fileName = "SizingMW" + MWOrder + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
     excelFile.Save(Server.MapPath(".") + "\\Temp\\" + fileName);
     OpenWindowLoadPdf = "window.open('/Temp/" + fileName + "', '_blank');";
}

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.