how to add watermark in xls sheet using code behind ?

Question

4.00/5 (1 vote)

See more:

Hi,

I am able to create .xls file successfully using stream writer and string builder and I am able to save it physically on the disk. Now my boss asked me to add a watermark in the background. How can I do that ?????
here is my code:

<pre lang="c#">
public static void ExportToFile(string filename, string filepath, System.Data.DataTable dtResult, string webServerFileLink)
    {
        string sFlag = "Error";

        StreamWriter sw = new StreamWriter(filepath, false);
        StringBuilder dataToExport = new StringBuilder();
        sw.WriteLine(Convert.ToString(dataToExport));
        
        try
        {
            if (filename.Trim() != "" && filepath != "" && dtResult.Rows.Count != 0)
            {
                 dataToExport.Append("<table width=\"100%\">");
                dataToExport.Append("<tr>");
               
                foreach (DataColumn dCol in dtResult.Columns)
                {
                    dataToExport.Append("<td style=\"border:#ccc 1px dotted; height:40px; vertical-align:middle;text-align:center;min-width:100px; background-color:#D0EFF5;\">");
                    dataToExport.Append(HttpContext.Current.Server.HtmlEncode(dCol.ColumnName));
                    dataToExport.Append("</td>");
                    
                }
             

                dataToExport.Append("</tr>");

                foreach (DataRow dRow in dtResult.Rows)
                {
                    dataToExport.Append("<tr>");
                    foreach (object obj in dRow.ItemArray)
                    {
                        dataToExport.Append("<td style=\"border:#ccc 1px dotted;min-width:100px; vertical-align:middle;text-align:center;\">");
                        dataToExport.Append(HttpContext.Current.Server.HtmlEncode(obj.ToString()));
                        dataToExport.Append("</td>");
                      
                    }
                   
                    dataToExport.Append("</tr>");
                }

                dataToExport.Append("</table>");
                
            }
            sw.WriteLine(Convert.ToString(dataToExport));
            sFlag = "Success";

            sw.Close();
           

        }



        catch (Exception ex)
        {
            throw ex;
        }


       
      
    }

Please Help !

Posted 24-Aug-12 23:23pm

Taresh Uppal

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2012-08-24T23:28:00

Solution 1

An xls is an Excel spreadsheet. I am not sure it supports watermarks, they tend to exist on bitmaps. The code you posted creates a table, which has nothing to do with Excel, unless you have code you didn't show that turns tables into an xls file. If you do have such a library, then it may have methods to do what you want, although I doubt it, as it does not seem to work with Excel directly. Hard to say from the code you posted. The Micrsoft Tools for Office let you interact with Office files in .NET. If watermarks exist at all, then perhaps that's where you can do it.

But given that an Excel file is not a bitmap, anything you add, an end use can remove.

Posted 24-Aug-12 23:28pm

Christian Graus

Comments

Taresh Uppal 25-Aug-12 5:35am

here is the rest of code:

string strDwnload_file_name = dwnlod_file_name(strWorkOrder);
ExportToFile(strDwnload_file_name, HttpContext.Current.Server.MapPath("~//download//" + strDwnload_file_name + ".xls"), dtResult, "http://www.cybex.in/ODMS//download" + "/" + strDwnload_file_name + ".xls");
//CreateCSVFile(dtResult, HttpContext.Current.Server.MapPath("~//download//" + strDwnload_file_name + ".csv"), "http://www.cybex.in/ODMS//download" + "/" + strDwnload_file_name + ".csv");
string Url_of_HomePage = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + HttpContext.Current.Request.ApplicationPath + "/";
//download_url = "" + Url_of_HomePage + "download/" + strDwnload_file_name + ".csv";
download_url = "" + Url_of_HomePage + "download/" + strDwnload_file_name + ".xls";

I do nothing but pick a unique name for downloading the file, convert it into url, add .xls and thats all.

Christian Graus 25-Aug-12 5:37am

There is no database code here, anywhere

Taresh Uppal 25-Aug-12 5:42am

string download_url = "";
int iResult = 0;
string strMonthName = Select_Month_Name(From);
string str_Search_Query_For_Data = search(strData, strHSCODE, strComb, strProd,strProdNot, From, To, strMonthName);

System.Data.DataTable dtResult = DAO_Con_diff_db.Select_datatable(conn_str_2012, str_Search_Query_For_Data);

if (dtResult.Rows.Count != 0)
{
iResult = 1;

if (strData == "EXPORT")
{
dtResult.Columns[5].ColumnName = "FOB VALUE INR";
}
else
{
dtResult.Columns[5].ColumnName = "ASSESSABLE VALUE INR";
}

string strDwnload_file_name = dwnlod_file_name(strWorkOrder);
ExportToFile(strDwnload_file_name, HttpContext.Current.Server.MapPath("~//download//" + strDwnload_file_name + ".xls"), dtResult, "http://www.cybex.in/ODMS//download" + "/" + strDwnload_file_name + ".xls");

string Url_of_HomePage = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + HttpContext.Current.Request.ApplicationPath + "/";

download_url = "" + Url_of_HomePage + "download/" + strDwnload_file_name + ".xls";

the data is fetched inside a datatable and than this datatable is modified in my menthods i showed u above to make it as a xls extension.

Taresh Uppal 25-Aug-12 5:43am

we make a search query for minning data as simple sql query which brings the data and than I take action accordingly...

Christian Graus 25-Aug-12 5:45am

So you don't STORE the data, you FETCH it. All of this has me lost. Where does your xls file come from ? What is creating it ? The code you posted writes a HTML table, not an xls file.

Taresh Uppal 25-Aug-12 5:54am

why u geting hyper on dat ??? I have already no I am not storing anything anywhere... I fetch the data in datatable.. pass it to the 'exporttofile' thats all I have in my code. I dont know what do u mean by where do i store it ?

rest of the code is this which invokes a download box:

string strResult = bl_Query_for_admin.Query_Data(strWorkOrder,strDataType, From, To,strHSCODE, strComb, strProd,strProdNot);
if (strResult != "")
{

lblMsg.Visible = false;
lblMsg.Text = "";

string strDownloadPageURL = strResult;
ScriptManager.RegisterStartupScript(this, typeof(string), "OPEN_WINDOW", "window.open( '" + strDownloadPageURL + "', null, 'height=400,width=800,status=yes,toolbar=no,menubar=no,location=no' );", true);

}

Query data is the method in my business logic where I have kept my above shown methods.

Taresh Uppal 25-Aug-12 5:55am

This is physically saved in download folder of my project thats it ...

Christian Graus 25-Aug-12 7:32am

My point is entirely that I can't help you without making sense of the question. There is no code here to generate an excel file. If you are generating an excel file, this table is not it. Therefore, if you want to generate a watermark on an excel file, my answer is correct, but it's also helpful to know if you're really creating one somewhere.

Taresh Uppal 25-Aug-12 7:48am

Alryt Sir, I apologize here. May be I am doing something wrong. I will try my best to learn more and will surely seek your help than. Thank you for your time. Sorry again !

Christian Graus 25-Aug-12 7:50am

The conversation does not have to end here. I'm happy to try to keep helping if you can answer my questions, although I am all for you doing a little debugging yourself, also. At this point, I am unsure what makes you think that the HTML table you're generating is an Excel file.

Taresh Uppal 27-Aug-12 0:29am

Well I am still doing it...the code that I showed you here is the all code I have which works for me. Its just that suddenly my boss's requirement changed and he asked me to add a water mark. Up to now everything was working fine and now I am stucked.

Taresh Uppal 27-Aug-12 1:55am

I just collect the data in my datatable and than pass it to exporttofile method which converts it into a HTML table and after that I save the file in download folder of my project and pass the address of a specific file to the client as URL which helps him getting the download. Can u please tell me any link to write the file using com and interop library so that I can write data from my data table into excel and create a watermark in at the same time
?

Taresh Uppal 27-Aug-12 6:14am

Somehow your scolding made my day. I am 22 years old and I sincerely appreciate the way you challenged me. What I did I saved the file on my server the same way I am doing than picked it up using interop.excel objects and after that made the modifications and saved it with a new name and deleted the old one. Thank you Sir, thanx a zillion times. I just wish my client will like it ! :)

Christian Graus 27-Aug-12 8:53am

That's awesome. It takes longer and makes me less popular at times, but I'd really like to encourage people to learn rather than just provide copy/paste solutions ( although in this case I was really just trying to get to the bottom of what you were doing ). Congratulations, it made my morning to read this and I'm glad you got it working.

Taresh Uppal 28-Aug-12 2:21am

This thread will always remail close to my heart and coding senses...:)