void Button2_Click(object sender, EventArgs e)
{
NpoiLib npl = new NpoiLib();
List<Row> list = npl.ReadExcel(Server.MapPath("text.xls"), 0);
Response.Write("<table><tr><td>111</td><td>222</td><td>333</td></tr>");
int i = 0;
string[] explicitListValues = new string[] { "AS", "CE CP", "SC IC", "Others" };
foreach (Row r in list)
{
if (r != null)
{
Cell c = r.GetCell(0);
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(0, 10, 0, 3));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(explicitListValues);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)c.Sheet).AddValidationData(dataValidation);
string v1 = npl.ReadRowData(r, 0);
string v2 = npl.ReadRowData(r, 1);
string v3 = npl.ReadRowData(r, 2);
Response.Write("<tr><td>" + v1 + "</td><td>" + v2 + "</td><td>" + v3 + "</td></tr>");
}
}
Response.Write("</table>");
}
void Button3_Click(object sender, EventArgs e)
{
NpoiLib npl = new NpoiLib();
List<Row> list = npl.ReadExcel(Server.MapPath("text.xls"), 0);
string[] data = new string[] { "AS", "CE CP", "SC IC", "Others" };
foreach (Row r in list)
{
npl.WriteDropDownList2(r.Sheet, 0, 0, 2, 3, data);
}
npl.SaveExcel(Server.MapPath("text2.xls"));
Response.Write("<script>alert('CreatSuc!');</script>");
}
void excel()
{
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("c1", typeof(string));
DataColumn dc2 = new DataColumn("c2", typeof(string));
DataColumn dc3 = new DataColumn("c3", typeof(string));
DataColumn dc4 = new DataColumn("c4", typeof(string));
DataColumn dc5 = new DataColumn("c5", typeof(string));
DataColumn dc6 = new DataColumn("c6", typeof(string));
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
dt.Columns.Add(dc6);
for (int i = 0; i < 20; i++)
{
DataRow dr = dt.NewRow();
dr[0] = new Random((int)DateTime.Now.Ticks).Next(10, 20).ToString();
dr[1] = new Random((int)DateTime.Now.Ticks - 222).Next(1000, 2000).ToString();
dr[2] = new Random((int)DateTime.Now.Ticks - 333).Next(1550, 20000).ToString();
dr[3] = new Random((int)DateTime.Now.Ticks - 444).Next(19990, 299900).ToString();
dr[4] = new Random((int)DateTime.Now.Ticks - 555).Next(1, 200).ToString();
dr[5] = new Random((int)DateTime.Now.Ticks - 666).Next(23, 45).ToString();
dt.Rows.Add(dr);
}
//create table;
NpoiLib npl = new NpoiLib();
Sheet sheet = npl.CreateSheet("sheet11");
string[] explicitListValues = new string[] { "AS", "CE CP", "SC IC", "Others" };
int i0 = 0;
foreach (DataRow dr in dt.Rows)
{
Row row = npl.CreateRow(sheet, i0);
i0++;
npl.WriteCell(row, i0, 0, dr[0].ToString());
npl.WriteCell(row, i0, 1, dr[1].ToString());
npl.WriteCell(row, i0, 2, dr[2].ToString());
npl.WriteCell(row, i0, 3, dr[3].ToString());
npl.WriteCell(row, i0, 4, dr[4].ToString());
npl.WriteCell(row, i0, 5, dr[5].ToString());
//NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
//ranglist.AddCellRangeAddress(new CellRangeAddress(0, 10, 0, 3));
//DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(explicitListValues);
//HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
//((HSSFSheet)sheet).AddValidationData(dataValidation);
}
npl.SaveExcel(Server.MapPath("text.xls"));
Response.Write("<script>alert(\"CreateSuc\");</script>");
}