//public static void test1()
//{
// HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
// CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
// DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
// HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
// sheet1.AddValidationData(dataValidate);
// MemoryStream ms = new MemoryStream();
// hssfworkbook.Write(ms);
// string workbookFile = @"D:\\wulei22.xls";
// hssfworkbook = null;
// FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// fs.Close();
//}
public static void setdownlist()
{
//创建工作簿
HSSFWorkbook ssfworkbook = new HSSFWorkbook();
//创建工作表(页)
HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
//创建一行
HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);
//设置表头
headerRow.CreateCell(0).SetCellValue("ID");
//设置表头的宽度
sheet1.SetColumnWidth(0, 15 * 256);
#region 添加显示下拉列表
HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
ssfworkbook.SetSheetHidden(1, true); //隐藏
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
// range.Reference = "ShtDictionary!$A$1:$A$3";//格式
range.NameName = "dicRange";
#endregion
headerRow.CreateCell(1).SetCellValue("Selected");
sheet1.SetColumnWidth(1, 15 * 256);
//将下拉列表添加
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
headerRow.CreateCell(2).SetCellValue("VALUE");
sheet1.SetColumnWidth(2, 15 * 256);
//写入数据
//创建数据行
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
//填充数据
dataRow.CreateCell(0).SetCellValue("1"); //id
dataRow.CreateCell(1).SetCellValue(""); //选择框
dataRow.CreateCell(2).SetCellValue("值"); //选择框
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ssfworkbook.Write(ms);
string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
object Response = null;
string workbookFile = @"D:\\wulei.xls";
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
//Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
}
public void TestNames()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
wb.GetNameAt(0);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("no defined names"), e.Message);
}
HSSFName name = (HSSFName)wb.CreateName();
Assert.IsNotNull(name);
Assert.IsNull(wb.GetName("somename"));
name.NameName = ("myname");
Assert.IsNotNull(wb.GetName("myname"));
Assert.AreEqual(0, wb.GetNameIndex(name));
Assert.AreEqual(0, wb.GetNameIndex("myname"));
try
{
wb.GetNameAt(5);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
try
{
wb.GetNameAt(-3);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
}
public static void test1()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = hssfworkbook.CreateName() as HSSFName;
range.RefersToFormula = "ShtDictionary!$A1:$A3";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
string workbookFile = @"D:\\wulei1.xls";
hssfworkbook = null;
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
}
public static void test3()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
HSSFRow dataRow = sheet2.CreateRow(0) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("省份");
dataRow.CreateCell(1).SetCellValue("湖北");
dataRow.CreateCell(2).SetCellValue("湖南");
dataRow.CreateCell(3).SetCellValue("广东");
dataRow = sheet2.CreateRow(1) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("湖北");
dataRow.CreateCell(1).SetCellValue("汉口");
dataRow.CreateCell(2).SetCellValue("汉阳");
dataRow.CreateCell(3).SetCellValue("武昌");
dataRow = sheet2.CreateRow(2) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("湖南");
dataRow.CreateCell(1).SetCellValue("长沙");
dataRow.CreateCell(2).SetCellValue("岳阳");
dataRow.CreateCell(3).SetCellValue("长沙南");
dataRow = sheet2.CreateRow(3) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("广东");
dataRow.CreateCell(1).SetCellValue("深圳");
dataRow.CreateCell(2).SetCellValue("广州");
dataRow.CreateCell(3).SetCellValue("广州东");
// sheet2.IsRightToLeft = false;
IName range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "省份"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B1:D1";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "湖北"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B2:D2";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "湖南"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B3:D3";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "广东"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B4:D4";
//range1.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
// "ShtDictionary",
// "4",
// 2,
// "A");
// var colName = GetExcelColumnName(1);
HSSFName range = hssfworkbook.CreateName() as HSSFName;
// range.RefersToFormula = "ShtDictionary!$B1:D1";
range.RefersToFormula = "ShtDictionary!$A1:A4";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
regions = new CellRangeAddressList(0, 0, 1, 1);
DVConstraint constraint1 = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", "A", 1));
// constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
dataValidate = new HSSFDataValidation(regions, constraint1);
sheet1.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(2, 2, 0, 0);
// constraint1 = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", "C", 2));
//// constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
//dataValidate = new HSSFDataValidation(regions, constraint1);
//sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
string workbookFile = @"D:\\8888.xls";
sheet2 = null;
hssfworkbook = null;
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
}
