Read Pdf form field and convert to excel using asp.net ,C#

convert Pdf  form field and to excel file using asp.net ,C#

Download itextsharp.dll file

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Reflection;

using Acrobat;

using System.Collections;

using iTextSharp.text.pdf;

using System.IO;

using System.Data.SqlClient;

private void button3_Click(object sender, EventArgs e)

{

string pdfTemplate = "C:/Users/yudi/Desktop/pdftoexcelsunil.pdf";

PdfReader pdfReader = new PdfReader(pdfTemplate);

AcroFields fields = pdfReader.AcroFields;

string fval = fields.GetField("First name");

string lval = fields.GetField("last name");

string address = fields.GetField("address");

string city = fields.GetField("city");

string state = fields.GetField("state");

string email = fields.GetField("email");

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=gltest;User ID=sunil;Password=sunil");

con.Open();

SqlCommand cmd = new SqlCommand("insert into pdftable(firstname,lastname,address,city,state,email) values('"+fval+"','"+lval+"','"+address+"','"+city+"','"+state+"','"+email+"') ",con);

cmd.ExecuteNonQuery();

con.Close();

int coun=   pdfReader.AcroForm.Fields.Count;

//PdfDocument pdfdoc = new PdfDocument();

byte[] bytes = System.IO.File.ReadAllBytes("C:/Users/yudi/Desktop/pdftoexcelsunil.pdf");

System.IO.File.WriteAllBytes("C:/Users/yudi/Desktop/newpdftoexcelsunil.xls", bytes);

save("newpdftoexcelsunil");

}

public void save(string filename)

{

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=gltest;User ID=sunil;Password=sunil");

con.Open();

//try

{

System.Windows.Forms.SaveFileDialog opendlg = new System.Windows.Forms.SaveFileDialog();

opendlg.FileName = filename;

opendlg.Filter = "Excel Spreadsheets (*.xls)|*.xls";

opendlg.DefaultExt = "xls";

if (opendlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)

{

StringBuilder sbrHTML = new StringBuilder();

SqlCommand dc = new SqlCommand("select * from pdftable", con);

SqlDataReader dr;

dr = dc.ExecuteReader();

//Making HTML

sbrHTML.Append("

");

sbrHTML.Append("

");

sbrHTML.Append("

");

for (int i = 0; i < dr.FieldCount; i++)

{

sbrHTML.Append("

");

}

sbrHTML.Append("

");

while (dr.Read())

{

sbrHTML.Append("

");

for (int i = 0; i < dr.FieldCount; i++)

{

sbrHTML.Append("

");

}

sbrHTML.Append("

");

}

dr.Close();

sbrHTML.Append("

sql data
" + dr.GetName(i) + "
" + good_value(dr.GetValue(i).ToString()) + "

");

sbrHTML.Append("

Reported by sunil");

sbrHTML.Append("

Date: " + System.DateTime.Now + "");

//ENDOF MAKING HTML

//WRITING AS AN XSL

StreamWriter swXLS = new StreamWriter(opendlg.FileName);

swXLS.Write(sbrHTML.ToString());

swXLS.Close();

con.Close();

}

}

}

private string good_value(string input)

{

return ((input == "" || (input[0] >= '0' && input[0] <= '9')) ? "'" : "") + input;

}

Read more ...

Read excel file and add new colunm in excel using asp.net ,C#

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Windows;

using System.Xml;

using System.Collections;

using System.IO;

using System.Globalization;

using System.Data;

using System.Data.OleDb;

using System.Reflection;

using System.Resources;

using System.Globalization;

using Microsoft.Office.Interop;

using Excel = Microsoft.Office.Interop.Excel;

private void button1_Click(object sender, EventArgs e)

        {

            saveexl();

        }

public void saveexl()

        {

            string strExcelFilePath = @"D:\\fdlog.xlsx";

            DataTable dt = new DataTable();

            string conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFilePath + ";Extended Properties=Excel 8.0";

            OleDbConnection connExcel = new OleDbConnection(conStr);

            OleDbCommand cmdExcel = new OleDbCommand();

            OleDbDataAdapter oda = new OleDbDataAdapter();

            cmdExcel.Connection = connExcel;

            //Get the name of First Sheet

            connExcel.Open();

            DataTable dtExcelSchema;

            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

            connExcel.Close();

            //Read Data from First Sheet

            connExcel.Open();

            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

            oda.SelectCommand = cmdExcel;

            oda.Fill(dt);

            connExcel.Close();

            string temp = "";

            string filePath = @"D:\\sss.csv";

            string delimiter = "~";

            StringBuilder sb = new StringBuilder();

            DateTime dat = DateTime.Now;

            Microsoft.Office.Interop.Excel.Worksheet sheet = new Microsoft.Office.Interop.Excel.Worksheet();

            object misValue = System.Reflection.Missing.Value;

            string workbookPath = "D:/fdlog.xlsx";

            //Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(misValue);

            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            string currentSheet = "fdlog";

                    Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);

            for (int intRowIndex = 1; intRowIndex < dt.Rows.Count; intRowIndex++)

            {

                try

                {

                    string text = dt.Rows[intRowIndex][1].ToString();

                    string code = dt.Rows[intRowIndex][0].ToString();

                                        

                    excelWorksheet.Cells[intRowIndex, 4] = text;

               

                }

                catch (Exception ex)

                {

                  

                }

            }

            string filename = "fdlog-sunil";

            excelWorkbook.SaveAs(filename + ".xlsx", misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);

            excelWorkbook.Close(true, misValue, misValue);

            excelApp.Quit();

            releaseObject(excelWorksheet);

            releaseObject(excelWorkbook);

            releaseObject(excelApp);

            System.IO.File.WriteAllText(filePath, sb.ToString());

          

        }

  private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

Read more ...