Sunday, November 20, 2011

Export GridView to PDF using ITextSharp

Copy from this site http://csharpdotnetfreak.blogspot.com/2009/01/itextsharp-export-paging-gridview-pdf.html

itextsharp.dll in Bin folder of Application 

using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html;
 
protected void btnExport_Click(object sender, EventArgs e)
{
int columnCount = GridView1.Columns.Count;
int rowCount = GridView1.Rows.Count;
int tableRows = rowCount + 3;
iTextSharp.text.Table grdTable=
new iTextSharp.text.Table(columnCount, tableRows);
grdTable.BorderWidth = 1;
grdTable.BorderColor = new Color(0, 0, 255);
grdTable.Cellpadding = 5;
grdTable.Cellspacing = 5;
Cell c1 = new Cell("Exporting paging enabled GridView to PDF example");
c1.Header = true;c1.Colspan = 2;
grdTable.AddCell(c1);
Cell c2 = new Cell("By amiT jaiN , amit_jain_online@yahoo.com");
c2.Colspan = 2;
grdTable.AddCell(c2);
grdTable.AddCell("Name");
grdTable.AddCell("Location");
for (int rowCounter = 0;rowCounter < rowCount; rowCounter++)
{
for (int columnCounter = 0;columnCounter < columnCount; columnCounter++)
   {
string strValue =GridView1.Rows[rowCounter].Cells[columnCounter].Text;
     grdTable.AddCell(strValue);
   }
}
Document Doc = new Document();
PdfWriter.GetInstance(Doc, Response.OutputStream);
Doc.Open();
Doc.Add(grdTable);
Doc.Close();
Response.ContentType = "application/pdf";
Response.AddHeader
("content-disposition", "attachment; filename=AmitJain.pdf");
Response.End();
}
  2nd Example
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using System.IO;
using System.Collections;
using System.Net;
 
Now in Click event of button i m creating a new 
HtmlForm and adding the gridview control to this form in code behind , 
than creating instance of StringWriter class and HtmlTextWriter to write strings and than rendernig these to form created earlier
  
protected void btnExport_Click(object sender, EventArgs e)
{
HtmlForm form = new HtmlForm();
form.Controls.Add(GridView1);
StringWriter sw = new StringWriter();
HtmlTextWriter hTextWriter = new HtmlTextWriter(sw);
form.Controls[0].RenderControl(hTextWriter);
string html = sw.ToString();
Document Doc = new Document();

//PdfWriter.GetInstance
//(Doc, new FileStream(Request.PhysicalApplicationPath 
//+ "\\AmitJain.pdf", FileMode.Create));

PdfWriter.GetInstance
(Doc, new FileStream(Environment.GetFolderPath
(Environment.SpecialFolder.Desktop)
+ "\\AmitJain.pdf", FileMode.Create));
Doc.Open();

Chunk c = new Chunk
("Export GridView to PDF Using iTextSharp \n",
FontFactory.GetFont("Verdana", 15));
Paragraph p = new Paragraph();
p.Alignment = Element.ALIGN_CENTER;
p.Add(c);
Chunk chunk1 = new Chunk
("By Amit Jain, amit_jain_online@yahoo.com \n",
FontFactory.GetFont("Verdana", 8));
Paragraph p1 = new Paragraph();
p1.Alignment = Element.ALIGN_RIGHT;
p1.Add(chunk1);

Doc.Add(p);
Doc.Add(p1);

System.Xml.XmlTextReader xmlReader =
new System.Xml.XmlTextReader(new StringReader(html));
HtmlParser.Parse(Doc, xmlReader);

Doc.Close();
string Path = Environment.GetFolderPath
(Environment.SpecialFolder.Desktop)
+ "\\AmitJain.pdf";

ShowPdf(Path);
}

private void ShowPdf(string strS)
{
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
Response.AddHeader
("Content-Disposition","attachment; filename=" + strS);
Response.TransmitFile(strS);
Response.End();
//Response.WriteFile(strS);
Response.Flush();
Response.Clear();
}
 
 3rd Example 
//Format of table
private void ExportToPDF()
{
  Document document = new Document(PageSize.A4, 0, 0, 50, 50);
  System.IO.MemoryStream msReport = new System.IO.MemoryStream();

  try {
    // creation of the different writers
    PdfWriter writer = PdfWriter.GetInstance(document, msReport);

    // we add some meta information to the document
    document.AddAuthor("eJuly");
    document.AddSubject("Export to PDF");

    document.Open();

    iTextSharp.text.Table datatable = new iTextSharp.text.Table(7);

    datatable.Padding = 2;
    datatable.Spacing = 0;

    float[] headerwidths = { 6, 20, 32, 18, 8, 8, 8 };
    datatable.Widths = headerwidths;

    // the first cell spans 7 columns
    Cell cell = new Cell(new Phrase("System Users Report", FontFactory.GetFont(FontFactory.HELVETICA, 16, Font.BOLD)));
    cell.HorizontalAlignment = Element.ALIGN_CENTER;
    cell.Leading = 30;
    cell.Colspan = 7;
    cell.Border = Rectangle.NO_BORDER;
    cell.BackgroundColor = new iTextSharp.text.Color(System.Drawing.Color.Gray);
    datatable.AddCell(cell);

    // These cells span 2 rows
    datatable.DefaultCellBorderWidth = 1;
    datatable.DefaultHorizontalAlignment = 1;
    datatable.DefaultRowspan = 2;
    datatable.AddCell("No.");
    datatable.AddCell(new Phrase("Full Name", FontFactory.GetFont(FontFactory.HELVETICA, 14, Font.NORMAL)));
    datatable.AddCell("Address");
    datatable.AddCell("Telephone No.");

    // This cell spans the remaining 3 columns in 1 row
    datatable.DefaultRowspan = 1;
    datatable.DefaultColspan = 3;
    datatable.AddCell("Just Put Anything");

    // These cells span 1 row and 1 column
    datatable.DefaultColspan = 1;
    datatable.AddCell("Col 1");
    datatable.AddCell("Col 2");
    datatable.AddCell("Col 3");

    datatable.DefaultCellBorderWidth = 1;
    datatable.DefaultRowspan = 1;

    for (int i = 1; i < 20; i++) {
      datatable.DefaultHorizontalAlignment = Element.ALIGN_LEFT; 
      datatable.AddCell(i.ToString()); 
      datatable.AddCell("This is my name."); 
      datatable.AddCell("I
have a very, very, very, very, very, very, very, very, very, very,
very, very, very, very, very, very, very, very, very, very, very, very,
very, very, very, very, very long long address."); 
      datatable.AddCell("0123456789"); 

      datatable.DefaultHorizontalAlignment = Element.ALIGN_CENTER;
      datatable.AddCell("No"); 
      datatable.AddCell("Yes"); 
      datatable.AddCell("No"); 
    } 

    document.Add(datatable); 
  } 
  catch (Exception e) { 
    Console.Error.WriteLine(e.Message); 
  } 

  // we close the document 
  document.Close(); 

  Response.Clear(); 
  Response.AddHeader("content-disposition", "attachment;filename=Export.pdf"); 
  Response.ContentType = "application/pdf"; 
  Response.BinaryWrite(msReport.ToArray()); 
  Response.End(); 
} 
 

Fast Exporting from Dataset and more than 65536 rows


Important:    outputPath file extention must be xls. like "Test.xls" Because In excel 2007 (office 12), while it is writing to file (at SaveAs) Compatibility Checker opens and ask me to continue. i Used excelApp.DisplayAlerts = false; but when i open excel document, i saw just 65536 rows.


public static void ExportToExcel(DataSet dataSet, string outputPath)
    {
  
        System.Type moAppType;
        // if u use excell 2003 and 2007 on same project, make sure true object u loaded.
        moAppType = System.Type.GetTypeFromProgID("Excel.Application.12");
       
       // xlApp = (Excel.Application)moApp;
        // Create the Excel Application object
        Application excelApp = (Microsoft.Office.Interop.Excel.Application)System.Activator.CreateInstance(moAppType);
        excelApp.DisplayAlerts = false;
        
 
        // Create a new Excel Workbook
        Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
 
        int sheetIndex = 0;
 
        // Copy each DataTable
        foreach (System.Data.DataTable dt in dataSet.Tables)
        {
 
            // Copy the DataTable to an object array
            object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
 
            // Copy the column names to the first row of the object array
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                rawData[0, col] = dt.Columns[col].ColumnName;
            }
 
            // Copy the values to the object array
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                for (int row = 0; row < dt.Rows.Count; row++)
                {
                    rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                }
            }
 
            // Calculate the final column letter
            string finalColLetter = string.Empty;
            string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            int colCharsetLen = colCharset.Length;
 
            if (dt.Columns.Count > colCharsetLen)
            {
                finalColLetter = colCharset.Substring(
                    (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
            }
 
            finalColLetter += colCharset.Substring(
                    (dt.Columns.Count - 1) % colCharsetLen, 1);
 
            // Create a new Sheet
            Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
                excelWorkbook.Sheets.get_Item(++sheetIndex),
                Type.Missing, 1, XlSheetType.xlWorksheet);
 
            excelSheet.Name = dt.TableName;
            
 
            // Fast data export to Excel
            string excelRange = string.Format("A1:{0}{1}",
                finalColLetter, dt.Rows.Count + 1);
 
            excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
 
            // Mark the first row as BOLD
            ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
        }
       
        // Save and Close the Workbook
        
        excelWorkbook.SaveAs(outputPath, XlFileFormat.xlExcel12, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        excelWorkbook.Close(true, Type.Missing, Type.Missing);
        excelWorkbook = null;
 
        // Release the Application object
        excelApp.Quit();
        excelApp = null;
 
        // Collect the unreferenced objects
        GC.Collect();
        GC.WaitForPendingFinalizers();
 
    }

Monday, November 7, 2011

Hack Microsoft Access Password

Accesspv software is awesome for retrieving MS Access Password. But there is some limitation. Follow this site  www.nirsoft.net/utils/accesspv.html

Saturday, November 5, 2011

Gantt Chart (Range Bar Chart) by SSRS 2008

Copied from this site:  http://pnarayanaswamy.blogspot.com/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

 

Range Bar Chart (Gantt Chart) using SSRS 2008

SSRS 2008 – Range Bar Chart (GANTT CHART)– Step By Step

Main Criteria for Range Bar Chart is the data that is used to display in the graph.
The dataset that is used should return at least three details.
·         Task Name
·         Start Date,
·         Duration or End Date for the task.
Assuming that my dataset returns all the three details, I am going ahead to create the Gantt Chart.
My dataset returns  TaskName, StartDate, EndDate.
From Tool window, drop the Chart control in report body and you will see the below window, and select Range Bar chart as heighted below.

After clicking OK, this what you see in the report body.
Let’s discuss something about what we see here.
Category Fields:
This is the area where you will drop your fields to group the data, here I dropped the TaskName. So data is now grouped by TaskName. You can have multiple fields in category fields. I tried with 4 categories, and it worked like a champ.
Right click on the dropped category fields, select Category Group Properties.
Here you can control the label text.
Data Fields:
This is the section where we specify our start and end dates for the tasks. The fields you drop here has High and Low values (like a pair High, Low values).  It’s highlighted in the image below.

Now Right Click on the dropped, and field and select Series Properties. This is the very important information that needs to be updated.
Here Top value can be EndDate or (Duration + StartDate) and the Low value is StartDate.  You can click on Fill and specify a color for this series Bar.
Now Click on the dropped field, and it will select the series in the graph. Right click on this and select Show Data Labels.
Interestingly SSRS 2008 is very flexible in showing the labels, and colors for the bar dynamically based on expressions you define.  This is screen you see after selecting the Show Data Fields.
Customizing the series bar:
SSRS 2008 provides wide range of options to control the data series bar. I am going to discuss a few here and you can explore more.
Let me start with the label text. By default it shows the date as the label text. But in most of the cases we may need to show the Task Name or Subtask Name. Here what you need to do. Select the data series by clicking on the graph data series bar or clicking on the data fields section and open the Properties window (not the right click series properties) (Ctrl W P).

If you observe there is Boolean field UseValueAsLabel with value True. Because of this, it shows the date as label text by default. If you want custom label text, then change this to false and change the Label field here. I changed it to display TaskName by changing the Label value to “Fields!TaskName.Value”. You can have custom expression to populate the label. You can also customize the font colors, font widths, formatting borders for this label in this label section.
Now we move to the CustomAttributes section. This is very important part for the Gantt Chart for customization. Now let’s see what is there in it and how you can modify according to our requirements. I am discussing only the options I have explored till now.
BarLabelStyle:
This controls the label text alignment. Available values or Center,Left,Right,Outside. Selecting outside will show the label, just before the data series bar. You can select any value, and the default value is Center.
DrawingStyle:
This controls the drawing style like cylinder type bar or regular bar. Here is the list:
DrawSideBySide:
This is very interesting attribute. Here is example where we can use it.
I have a task with multiple subtasks and all my sub tasks are sequential (like first task end date is second task start date). In this case I want to show all the bars in same line, and with different colors. By setting value to “False”, this will allow us to show all data series bars related same grouping category to be displayed in the same line like shown below.

To view all the sub task data series group in one line, you need to set this DrawSideBySide to False for all the sub task data series.
PointWidth:
This is the field that controls the data series bar width. Maximum value is 1, and minimum value is 0. As you can see the default value is 0.8. If you want see like a line, then you can give like 0.05 or 0.1
In the same properties window, you can customize any field like Color with custom expressions.
Customizing the Axis Properties:
Right click on the Value Axis (X-Axis), select Value Axis Properties.
Under Axis Options, you have various options to customize the labels to be shown. By default it will show the date in short date format.
If you run the report, by default it will show only alternate labels both in X and Y axis. To view all the labels you need to check the check box Enable Variable interval. This is very common thing we may tend to forget.
You can give a specific Minimum, and Maximum values. You can specify an expression like Minmum is MIN(Fields!Start.Value) and Maximum is MAX(Fields!EndDate.Value).
Interval, Interval Type: I guess the name says it all. What is the interval (digit) and what is the interval type (Hours, Minutes, Weeks, Days, ..) you want to show in the value axis.  Accordingly you can change the display format in Number section. If you want to show the dates in HH:MM format, you can change it like given below.
Or you can give any specific format you want.  You can check out the options available for Labels, Label font sections here.
If you select the value axis (x-axis) and go to properties window you will see a wide range of fields you can customize. You can specify minor, major grid intervals, with specific colors, widths. You can always hide the axis (both X and Y) and you can hide the minor major grids and also minor major grid ticks.
Major, Minor grids applicable to chart area and major, minor grid ticks applicable to the labels section of the grid
Most of the properties are applicable to Y Axis as well (Series Axis Properties).
And here is the final Report out I got with Interval type Hours.
You can always customize the legends for the chart.
Here are some Gantt Charts report outputs I developed using SSRS 2008 Range Bar Chart:

Another example:

The above gantt charts are developed using multiple GANTT Charts with the same set of data.