Export an Excel file with a drop-down box (data exceeding the length solution)

Note: The row numbers and column numbers marked in this document are subscript, starting from 0.

// Download the Template

Protected void btndownload_serverclick (Object sender, eventargs E)

{

// Generate an Excel template

Createexceltemp ();

// Set the path of the template to be opened

String Path = This. mappath ("~ /Baseinfo/temp/gabprodectinfomaintain.xls ");

// Open or save this template

Exceloute (this, PATH );

}

/// Generate an Excel template and open it
/// </Summary>
Private void createexceltemp ()

{
// Obtain the list of product groups in the database
List <productgroupmodel> productgroupinfo = productgroupbusiness. getuserallproductgroup (currentusercode );

List <string> productgrouplist = new list <string> ();

// Combine the product group name and number in the product information list into a column and add it to the productgrouplist set.

Foreach (productgroupmodel item in productgroupinfo)

{
Productgrouplist. Add (item. Name + "(" + item. productgroupcode + ")");

}
// Create an Excel template (modify the template)
// Create a template
Hssfworkbook = new hssfworkbook ();

Isheet realsheet = hssfworkbook. createsheet ("realsheet ");
Isheet hidden = hssfworkbook. createsheet ("hidden ");

Set sheet as the subscript in hssfworkbook to hide

Hssfworkbook. setsheethidden (1, true );

// Cyclically add all data to the 0th column in the hidden

// Note: to reference the data in hidden in the nth column of another sheet, insert the data in the data source to the nth column in hidden. The column numbers must be the same.

For (INT I = 0; I <productgrouplist. Count; I ++)

{
Hssfrow rowtemp = (hssfrow) Hidden. createrow (I );
Icell celltemp = rowtemp. createcell (0); // check the database to column 0th in hidden.
Celltemp. setcellvalue (productgrouplist [I]. tostring ());
}

// Set some hidden attributes
INAME namedcell = hssfworkbook. createname ();
Namedcell. namename = "hidden ";
Namedcell. referstoformula = "hidden! A1: A "+ productgrouplist. toarray (). length;

// Add data in hidden to constraint

Dvconstraint constraint = dvconstraint. createformulalistconstraint ("hidden ");

Cellrangeaddresslist Addresslist = NULL;
Hssfdatavalidation validation = NULL;

// Create rows and columns for realsheet

Irow row0 = realsheet. createrow (0 );
Row0.createcell (0). setcellvalue ("Other Gab product information maintenance ");
Irow row1 = realsheet. createrow (1 );
Row1.createcell (0). setcellvalue ("product group ");
Row1.createcell (1). setcellvalue ("Product Name ");
Row1.createcell (2). setcellvalue ("product price ");

// Set the column width in realsheet

Realsheet. setcolumnwidth (0, 8000 );
Realsheet. setcolumnwidth (1, 5000 );
Realsheet. setcolumnwidth (2, 5000 );

// Fill in information
Datatable dt = a_gabgabmerrelationbusiness.getgabproductinfo ();
If (DT! = NULL | DT. Rows. Count> 0)
{
For (INT I = 0; I <DT. Rows. Count; I ++)
{

// Reference the data in hidden in Column 2nd of row 0th of realsheet
Addresslist = new cellrangeaddresslist (I + 2, I + 2, 0, 0 );

Validation = new hssfdatavalidation (Addresslist, constraint );
Realsheet. addvalidationdata (validation );

Hssfrow rowtemp = (hssfrow) realsheet. createrow (I + 2 );
Icell celltemp = rowtemp. createcell (0 );
Celltemp. setcellvalue (Dt. Rows [I] ["productgroupname"]. tostring ());

Icell celltemp1 = rowtemp. createcell (1 );
Celltemp1.setcellvalue (Dt. Rows [I] ["productname"]. tostring ());

Icell celltemp2 = rowtemp. createcell (2 );
Celltemp2.setcellvalue (Dt. Rows [I] ["productprice"]. tostring ());
}
}
# Endregion

// Save the modified template
String savepath = This. mappath ("~ /Baseinfo/temp/gabprodectinfomaintain.xls ");
Using (filestream file = new filestream (savepath, filemode. Create ))
{

// Write the file to the savepath path
Hssfworkbook. Write (File );

}

}

// Open or save the filename File

Public static void exceloute (page, string filename)
{
Httpresponse response = page. response;
Httpserverutility Server = page. server;

String fn = server. urldecode (filename );
Filestream = new filestream (FN, filemode. Open );
Long filesize = filestream. length;
Filestream. Close ();
Response. Clear ();
Response. Buffer = true;
Response. charset = "gb2312 ";
Response. appendheader ("content-disposition", "attachment?filename=sheet1.xls ");
Response. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Response. contenttype = "application/MS-excel ";
Response. addheader ("Content-Length", filesize. tostring ());
Page. enableviewstate = false;
Response. writefile (FN );
Response. Flush ();
Response. End ();
}