locked
Exporting SQL query output to MS-Word with special formatting RRS feed

  • Question

  • User-1246464706 posted

    Hi Experts ,

    Using SQL and C#.

    I have a project table with has ProjectName, ProjectDescription, ProjectTask,Employee and Date. A project can have many task and every task has a designated employee and a Date.

    I need to export a sql query result to Microsoft Word in the following format.

     

    Format:

    EmployeeName

             Project Name

                      ProjectTask

     

    Eg:

    Peter

            Project-A

                      - ProjectTask 1

                      - ProjectTask 2

                      - ProjectTask 3

            Project -B

                      - ProjectTask 1

                      - ProjectTask 2

    Ann

            Project-A

                      - ProjectTask 6               

            Project -D

                      - ProjectTask 1

                      - ProjectTask 2

     

    ......

     

    Here is the code I have written to export to Word from the webpage.( without any special formatting)

     

    void ExportWord()
        {
            string sql = querySelectionSummaryExport();
            DataSet ds = new DataSet();
            DBConnect db = new DBConnect();
            ds = db.getDataForgrid(sql);
            HttpResponse response = HttpContext.Current.Response;
            
            response.Clear();
            response.Charset = "";
            
            response.ContentType = "application/vnd.word";
            response.AddHeader("Content-Disposition", "attachment;filename=ExportData.doc");
    
            // create a string writer
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    // instantiate a datagrid
                    DataGrid dg = new DataGrid();
                    dg.DataSource = ds.Tables[0];
                    dg.DataBind();
                    dg.RenderControl(htw);
                    response.Write(sw.ToString());
                    response.End();
                }
            }
        }
      

     

    How can I get the data in the format I need from the above code?

    Some code guidance would be great.

     

    Thanks

    Tuesday, March 1, 2011 9:59 PM

Answers

  • User890154313 posted

    Tasks running several lines presents a challenge.  If you absolutely must have uniform formatting then I would recommend parsing the value into a fixed length, say 50 characters across or however much space you have.  Of course doing a blanket parse like that could cause words to be split in 2 so you'll either have to look for the closest space or just hyphenate the word.

    As for your second problem, that's far more easy.  Using a Replace statement we can switch the "<br/>" tag for a new line.

    I haven't tested this, but try using this code:

    public void ExportWord()
    { DataSet ds = new DataSet();
      DBConnect db = new DBConnect();
    
      String employee = String.Empty;
      String tEmployee = String.Empty;
      String project = String.Empty;
      String tProject = String.Empty;
      String tTask = String.Empty;
      String partialData = String.Empty;
    
      List<String> taskData = new List<String>();
    
      ds = db.getDataForgrid(querySelectionSummaryExport());
    
      StringBuilder output = new StringBuilder();
    
      Int32 lineSize = 50;
      
      foreach(TableRow row in ds.Tables[0].Rows)
      { tEmployee = row["Employee"].ToString();
        tProject = row["Project"].ToString();
        tTask = row["Task"].ToString();
    
        taskData.Clear();
          if((tTask.Length<=lineSize) && (!tTask.Contains("<br/>"))) taskData.Add(tTask);
          else
          { String[] sectionSplit = tTask.Split(new Char[]{'<','b','r','/','>'});
              foreach(String section in sectionSplit)
              { if(section.length<=lineSize) taskData.Add(section);
                else
                { for(Int32 i=0; i<=section.Length;)
                  { partialData = section.Substring(i, lineSize);
                      if((partialData.Length==lineSize) && (!partialData.EndsWith(" ")))
                      { while(!partialData.EndsWith(" ")) partialData = partialData.Substring(0, partialData.Length-1);
                      }
                    
                    taskData.Add(partialData);
                    startIndex+=partialData.Length;
                  }
                }
                
                taskData.Add("\n");
              }
          }
    
        if(employee!=tEmployee)
        { output.AppendFormat("{0}\n", tEmployee);
          employee = tEmployee;
        }
        if(project!=tProject)
        { output.AppendFormat("\t{0}\n", tProject);
          project = tProject;
        } 
        foreach(String item in TaskData)
        { if(item=="\n") output.Append(item);
          else
          { output.AppendFormat("\t\t{0}\n", item);
          }
        }
      }
    
      HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.Charset = "";
        response.ContentType = "application/vnd.word";
        response.AddHeader("Content-Disposition", "attachment;filename=ExportData.doc");
        response.Write(output.ToString());
        response.End();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2011 2:24 PM

All replies

  • User890154313 posted

    I would recommend looping the returned DataSet and using a StringBuilder object to create the document you've described.  You can use String escape characters \n = new line \t = tab to get the layout you want.

    After you have the document created just write the StringBuilder and you should be done.

    Wednesday, March 2, 2011 9:09 AM
  • User-1246464706 posted

    Any code help or sample code would be great.

     

    Thanks

    Wednesday, March 2, 2011 9:31 AM
  • User890154313 posted

    I'm going to work on the theory that the DataSet is already being sorted by Employee, Project, Task so I would expect the code to look something like this:

    public void ExportWord()
    { DataSet ds = new DataSet();
      DBConnect db = new DBConnect();
    
      String employee = String.Empty;
      String tEmployee = String.Empty;
      String project = String.Empty;
      String tProject = String.Empty;
      String tTask = String.Empty;
    
      ds = db.getDataForgrid(querySelectionSummaryExport());
    
      StringBuilder output = new StringBuilder();
    
      foreach(TableRow row in ds.Tables[0].Rows)
      { tEmployee = row["Employee"].ToString();
        tProject = row["Project"].ToString();
        tTask = row["Task"].ToString();
    
        if(employee!=tEmployee)
        { output.AppendFormat("{0}\n", tEmployee);
          employee = tEmployee;
        }
        if(project!=tProject)
        { output.AppendFormat("\t{0}\n", tProject);
          project = tProject;
        } 
        output.AppendFormat("\t\t- {0}\n", tTask);
      }
    
      HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.Charset = "";
        response.ContentType = "application/vnd.word";
        response.AddHeader("Content-Disposition", "attachment;filename=ExportData.doc");
        response.Write(output.ToString());
        response.End();
    }

    Wednesday, March 2, 2011 10:12 AM
  • User-1246464706 posted

    Nice.

    Does the job. Thanks

     

    I have 2 questions though which I'm unable to resolve.

     

    1) Task can be of several lines. The Task starts with a 2 tabs in the first line. If it runs to the second line then it starts from the beginin and not from 2 tabs. Similarly if the Task runs to the 3rd line starts from the begining. I need some horizontal alignment of 2 tabs for each new line in Task.

     

    2) Task can have line returns like enter. So in the database I use to store them as <br/>. Now need to eliminate those and to add a new line when ever I face those. How can I do this?

     

    Thanks

     

    Wednesday, March 2, 2011 12:56 PM
  • User890154313 posted

    Tasks running several lines presents a challenge.  If you absolutely must have uniform formatting then I would recommend parsing the value into a fixed length, say 50 characters across or however much space you have.  Of course doing a blanket parse like that could cause words to be split in 2 so you'll either have to look for the closest space or just hyphenate the word.

    As for your second problem, that's far more easy.  Using a Replace statement we can switch the "<br/>" tag for a new line.

    I haven't tested this, but try using this code:

    public void ExportWord()
    { DataSet ds = new DataSet();
      DBConnect db = new DBConnect();
    
      String employee = String.Empty;
      String tEmployee = String.Empty;
      String project = String.Empty;
      String tProject = String.Empty;
      String tTask = String.Empty;
      String partialData = String.Empty;
    
      List<String> taskData = new List<String>();
    
      ds = db.getDataForgrid(querySelectionSummaryExport());
    
      StringBuilder output = new StringBuilder();
    
      Int32 lineSize = 50;
      
      foreach(TableRow row in ds.Tables[0].Rows)
      { tEmployee = row["Employee"].ToString();
        tProject = row["Project"].ToString();
        tTask = row["Task"].ToString();
    
        taskData.Clear();
          if((tTask.Length<=lineSize) && (!tTask.Contains("<br/>"))) taskData.Add(tTask);
          else
          { String[] sectionSplit = tTask.Split(new Char[]{'<','b','r','/','>'});
              foreach(String section in sectionSplit)
              { if(section.length<=lineSize) taskData.Add(section);
                else
                { for(Int32 i=0; i<=section.Length;)
                  { partialData = section.Substring(i, lineSize);
                      if((partialData.Length==lineSize) && (!partialData.EndsWith(" ")))
                      { while(!partialData.EndsWith(" ")) partialData = partialData.Substring(0, partialData.Length-1);
                      }
                    
                    taskData.Add(partialData);
                    startIndex+=partialData.Length;
                  }
                }
                
                taskData.Add("\n");
              }
          }
    
        if(employee!=tEmployee)
        { output.AppendFormat("{0}\n", tEmployee);
          employee = tEmployee;
        }
        if(project!=tProject)
        { output.AppendFormat("\t{0}\n", tProject);
          project = tProject;
        } 
        foreach(String item in TaskData)
        { if(item=="\n") output.Append(item);
          else
          { output.AppendFormat("\t\t{0}\n", item);
          }
        }
      }
    
      HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.Charset = "";
        response.ContentType = "application/vnd.word";
        response.AddHeader("Content-Disposition", "attachment;filename=ExportData.doc");
        response.Write(output.ToString());
        response.End();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2011 2:24 PM