如何在ASP.NET C＃中将所有数据表数据导出到excel（spire.xlsx） [英] How to export all datatable data to excel(spire.xlsx) in ASP.NET C#

问题描述

大家好，

我想将数据表数据导出到excel.Here我使用spire.xls组件导出相同的内容。这里我用的是spire.xls becoz我要导出到excel的同时我必须在excel文件中添加超链接列。它工作得很完美，但它只显示数据表的前200条记录，而不是实际它有1250像。



请帮帮我...





提前感谢。



我尝试过：



查看我的示例代码

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;
            t.Dispose();


            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;
            list.Clear();
          
            ViewState["Path11"] = path11;
            book.SaveToFile(path11);

推荐答案

+ colnm +

2：+ colnm +

2:" + colnm + "

+ rowno1 +;

for（int i = 0; i< list.Count; i ++）
{
sheet.Range [range] .Cells [i] .Text = list [i];
}
foreach（SheetRange [range] .Cells中的CellRange cr ）
{
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]。文本=范围;
sheet.Range [R2]。文本= rg;
t.Dispose（）;


string nm = ViewState [Lo cationNm]的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;
list.Clear（）;

ViewState [Path11] = path11;
book.SaveToFile（path11）;

" + 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; t.Dispose(); 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; list.Clear(); ViewState["Path11"] = path11; book.SaveToFile(path11);

这篇关于如何在ASP.NET C＃中将所有数据表数据导出到excel（spire.xlsx）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！