Click here to Skip to main content
14,391,232 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello everyone,
I wan to export datatable data to excel.Here i used spire.xls component to export the same.Here i used spire.xls becoz i wan to export to excel at the same time i have to add hyperlink column along the excel file.It works perfectly but it only shows first 200 records of datatable instead of actually it has i.e 1250 like.

Please help me...

thanks in advance.

What I have tried:

See my sample code

t = (DataTable)ViewState["ExportData"];
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(t, true, 1, 1);
            String rg=sheet.AllocatedRange.RowCount.ToString();
            List<String> list = new List<string>();
            list = PathClass.pathlist;
            int colno = t.Columns.Count;
            int rowno = t.Rows.Count;
            string colnm = GetExcelColumnName(colno + 1);
            int rowno1 = rowno + 1;
            string range = "Sheet1!$" + colnm + "$2:" + colnm + "$" + rowno1 + "";

            for (int i = 0; i < list.Count; i++)
                sheet.Range[range].Cells[i].Text = list[i];
            foreach (CellRange cr in sheet.Range[range].Cells)
                Spire.Xls.HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range[cr.RangeAddress]);
                hylink1.Type = HyperLinkType.Url;
                hylink1.Address = cr.Text;

            sheet.Range["P1"].Text = list.Count.ToString();
            sheet.Range["Q1"].Text = t.Rows.Count.ToString();
            sheet.Range["R1"].Text = range;
            sheet.Range["R2"].Text = rg;

            string nm = ViewState["LocationNm"].ToString();
            string type = ViewState["BillType"].ToString();

            string p=txtBrowse.Text.Substring(0,txtBrowse.Text.Length-1);
            String p1=p+"\\"+"\\";
            string path11 =p1 + nm + "_" + type + ".xls";
            txtBrowse.Text = path11;
            ViewState["Path11"] = path11;
Updated 3-Jul-17 21:57pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

The component you're using is a commercial product. It sounds like you're using their "free" community edition, which is:
... limited to 5 sheets per workbook and 200 rows per sheet.

The page seems to suggest that this limit doesn't exist in v7.8 or higher, but I'm not entirely convinced that I've read that correctly.

If the latest "free" version still has the limit, then you'll either need to buy the commercial version, or use one of the many truly free and unlimited alternatives. For example:

Edit: As I suggested back in February[^]!
SujataJK 31-Mar-17 1:32am
thanks @Richard
Rate this:
Please Sign up or sign in to vote.

Solution 2

Example For Closed XML

string folderPath = "C:\\Export\\";
if (!Directory.Exists(folderPath))
//Codes for the Closed XML

using (XLWorkbook wb = new XLWorkbook())
    wb.Worksheets.Add(datatable, "Test_1");
    wb.SaveAs(folderPath + "Test_1.xlsx");


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100