excel[Answered] RSS

16 replies

Last post Oct 05, 2010 12:12 AM by mohit11

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
mohit11
Member

20 Points

305 Posts

excel

Sep 28, 2010 08:39 AM|mohit11|LINK

how to count no of non empty row and colum in excel sheet using c#.dont give me link give code.i tried

Microsoft.Office.Interop.Sheet sheet; sheet = Globals.ThisWorkbook.ActiveSheet

but it is not working pls give me solution
- Reply
thuhue
Contributor

2109 Points

2884 Posts

Re: excel

Sep 28, 2010 11:35 AM|thuhue|LINK

Please post more code.

Please mark this post as "Answer" and earn a point.
- Reply
sirdneo
Star

10444 Points

2463 Posts

Re: excel

Sep 30, 2010 04:05 AM|sirdneo|LINK

try using UsedRange property of Worksheet. It will give you the smallest grid on the worksheet which has all the content in it.

Also remember that if there are some spaces whcich you typed in excel, size of smallest grid will increase.

Thanks,
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Sep 30, 2010 11:45 PM|mohit11|LINK

how to use worksheet property from c#

sirdneo

Star

10444 Points

2463 Posts

Re: excel

Oct 01, 2010 12:14 AM|sirdneo|LINK

Here is little sample i created for you:-

Application _excelApp;
_excelApp = new Application();


Workbook workBook = _excelApp.Workbooks.Open("c:\\1.xls",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);


Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];


int rows = sheet.UsedRange.Rows.Count ;
int cols = sheet.UsedRange.Columns.Count

Thanks,
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~

- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 03, 2010 12:20 AM|mohit11|LINK

workBookIn and sheetNum i am not getting i used microsfot.excel name space but this two key word is not available int it it give error sheetnum is not avaiable
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 03, 2010 12:37 AM|mohit11|LINK

i tried this

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbok.Sheets[1];

it is giving rrows 65536 and colum 256 but i want non empty no of roe and colum pls suggest me
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 03, 2010 01:22 AM|mohit11|LINK

i tried your code it is giving 1 row and 1 colum always.ut in my excel sheet there r 4 row and 3 colum
- Reply
sirdneo
Star

10444 Points

2463 Posts

Re: excel

Oct 04, 2010 12:34 AM|sirdneo|LINK

See this post:-

http://excel.itags.org/microsoft-excel/332798/

THey used ActiveCell.SpecialCells(xlLastCell).Select to count no of rows.

Also if it is getting complex to count the no of rows in excel file, then generaly I check for first blank value. Whenever I found a blank, I just stop the further processing of the file. Why you are not using this simpler approach?

Thanks,
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 04, 2010 04:16 AM|mohit11|LINK

how to do ur approach
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 04, 2010 04:24 AM|mohit11|LINK

can u give me compete code to read non row active cell i m not getting

Microsoft.Office.Interop.Excel.ActiveCell.Special cell

i m not getting all this

sirdneo

Star

10444 Points

2463 Posts

Re: excel

Oct 04, 2010 06:05 AM|sirdneo|LINK

I have office 2007 installed on my machine and with below code I have correctly calculated rows and columns in excel file:-

protected void Page_Load(object sender, EventArgs e)
    {
        ApplicationClass Application = new ApplicationClass();
        Workbook wb = Application.Workbooks.Open("d:\\1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);


        Worksheet sheet = (Worksheet)wb.ActiveSheet;
        int rows = sheet.UsedRange.Rows.Count;
        int cols = sheet.UsedRange.Columns.Count;


    }

It should work, however if it does not work on your machine then you can refer to these links to read data from excel file:-

http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/ef11a193-54f3-407b-9374-9f5770fd9fd7 http://www.dreamincode.net/code/snippet1413.htm

Thanks,
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~

- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 04, 2010 06:59 AM|mohit11|LINK

ok i already mark u as answer .i am reading excel and convertin to pdf everything is gud but in excel 3 colum and 5 row

and in first row two colum r merged so i want when i read it automaticly undertstand two rows r merger and generate the same pdf using itextsharp
- Reply
sirdneo
Star

10444 Points

2463 Posts

Re: excel

Oct 04, 2010 07:47 AM|sirdneo|LINK

If you are using office 2007 then you can easily save Excel file as PDF.

See the official documentation on: http://msdn.microsoft.com/en-us/library/bb256835.aspx

Also you need to download this addin.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

Thanks,
Zeeshan Umar
~Please Mark As Answer, one or multiple posts, which helped you. So that it might be useful for others~
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 04, 2010 11:43 PM|mohit11|LINK

but i want to do it using c# code
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 04, 2010 11:46 PM|mohit11|LINK

i am developing my own convetor using C#.net i hava done all wem but problem is only in excel sheet as i describe problem above
- Reply
mohit11
Member

20 Points

305 Posts

Re: excel

Oct 05, 2010 12:12 AM|mohit11|LINK

i am developing a pdf using c#.

iTextSharp.text.Document Exceldoc = new iTextSharp.text.Document(iTextSharp.text.PageSize.LETTER, 10, 10, 42, 35);

            PdfWriter Excelwri = PdfWriter.GetInstance(Exceldoc, new    FileStream(Response.AppendHeader(""content-disposition"",""attachment; filename='"+TextBox1.Text+"'.pdf""), FileMode.Create));


            Exceldoc.Open();//Open Document to write
            iTextSharp.text.Font font8 = FontFactory.GetFont("ARIAL", 4);

            Excelparagraph.SpacingAfter = 15f;
            Exceldoc.Add(Excelparagraph);
            Exceldoc.Add(PdfTable);
            Exceldoc.Close();

pdf is created but i want when it create it should be created in download folder with save file dialog box for this i write

Response.AddHeader("content-disposition", "attachment; filename='"+TextBox1.Text+"'.pdf");

it it giving error in above line can ont convert void to string

and invalid argument in get instance

means my reqirement is only that in my web application a file is created a save file dialog box come and store file in download.i am getting error only n this line

Response.AddHeader("content-disposition", "attachment; filename='"+TextBox1.Text+"'.pdf");

‹ Previous Thread|Next Thread ›