A 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, 
TXT), 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:
[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’);”;
}
[/code]