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>");
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
ISheet sheet2 = hssfworkbook.CreateSheet("Sheet2");
//create three items in Sheet2
IRow row0 = sheet2.CreateRow(0);
ICell cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product1");
row0 = sheet2.CreateRow(1);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product2");
row0 = sheet2.CreateRow(2);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product3");
CellRangeAddressList rangeList = new CellRangeAddressList();
//add the data validation to the first column (1-100 rows)
rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3");
HSSFDataValidation dataValidation = new
HSSFDataValidation(rangeList, dvconstraint);
//add the data validation to sheet1
((HSSFSheet)sheet1).AddValidationData(dataValidation);
WriteToFile();
}
public void TestPLVRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(is1);
CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
// This used to throw an error before
try
{
workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
}
catch (InvalidOperationException)
{
Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
}
}
private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4);
var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint);
destinationWorksheet.AddValidationData(markdvNum_4);
}
private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" });
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" });
var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" });
var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3);
var markdv = new HSSFDataValidation(markColumn, markListConstraint);
var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6);
var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint);
var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8);
var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint);
var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5);
var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint);
var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7);
var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint);
var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9);
var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint);
var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10);
var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint);
destinationWorksheet.AddValidationData(markdv);
destinationWorksheet.AddValidationData(markULdv);
destinationWorksheet.AddValidationData(markSLdv);
destinationWorksheet.AddValidationData(markdvNum_5);
destinationWorksheet.AddValidationData(markdvNum_7);
destinationWorksheet.AddValidationData(markdvNum_9);
destinationWorksheet.AddValidationData(markdvNum_10);
}
public ActionResult Template(int? tagId)
{
var basicInfoSheetName = ProUploadService.BASIC_SHEET;
var moreInfoSheetName = ProUploadService.MORE_SHEET;
var supportSheetName = "不要修改";
var headerLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"商品名称",new {dataformat=0,width=20}},
{"描述",new {dataformat=0,width=50}},
{"吊牌价",new {dataformat=2,width=8}},
{"现价",new {dataformat=2,width=8}},
{"品牌名",new {dataformat=0,width=20}},
{ "分类名",new {dataformat=0,width=20}},
{"门店名",new {dataformat=0,width=20}},
{ "促销活动编码",new {dataformat=0,width=20}},
{ "专题编码（多个以,分割)",new {dataformat=0,width=20}},
{"可销售",new {dataformat=2,width=5}},
{"商品货号",new {dataformat=0,width=10}}
};
var workbook = new HSSFWorkbook();
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
headerLabelCellStyle.BorderRight = BorderStyle.THIN;
headerLabelCellStyle.BorderTop = BorderStyle.THIN;
headerLabelCellStyle.WrapText = true;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
//set support sheet
var supportSheet = workbook.CreateSheet(supportSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
Func<int, dynamic, int> supportFill = (rowIndex, data) =>
{
var brandRow = supportSheet.CreateRow(rowIndex++);
var brandCodeCol = brandRow.CreateCell(1);
brandCodeCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Id);
var brandNameCol = brandRow.CreateCell(2);
brandNameCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Name);
return rowIndex;
};
int brandRowIndex = 0;
foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
brandRowIndex = supportFill(brandRowIndex, brand);
}
int tagRowIndex = brandRowIndex;
var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
//if (tagId.HasValue)
// tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
foreach (var tag in tagLinq.OrderBy(t => t.Name))
{
tagRowIndex = supportFill(tagRowIndex, tag);
}
int storeRowIndex = tagRowIndex;
foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
storeRowIndex = supportFill(storeRowIndex, store);
}
//set basic sheet
var sheet1 = workbook.CreateSheet(basicInfoSheetName);
//workbook.SetSheetOrder(basicInfoSheetName, 0);
var rowFirst = sheet1.CreateRow(0);
Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
{
var cell = rowFirst.CreateCell(cellindex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(desc);
cell.CellStyle = headerLabelCellStyle;
sheet1.SetColumnWidth(cellindex, option.width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)option.dataformat;
sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
};
int index = 0;
foreach (var key in headerLabels.Keys)
{
cellSetting(index++, key, headerLabels[key]);
}
//set constraint
DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
branddataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(branddataValidation);
DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
tagdataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(tagdataValidation);
DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
storedataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(storedataValidation);
DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
is4saledataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(is4saledataValidation);
//set sheet2
//create property value sheet
var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
.Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
.OrderBy(b => b.P.CategoryId)
.ThenBy(b => b.P.PropertyDesc);
int propertyRowIndex = storeRowIndex;
foreach (var tag in tagLinq)
{
var propertyFromIndex = propertyRowIndex;
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
{
propertyRowIndex = supportFill(propertyRowIndex, property);
}
if (propertyRowIndex > propertyFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
rName.NameName = tag.Name;
}
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
{
int valueFromIndex = propertyRowIndex;
foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
{
propertyRowIndex = supportFill(propertyRowIndex, pvalue);
}
if (propertyRowIndex > valueFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
}
}
}
var moreheaderLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"属性名",new {dataformat=0,width=20}},
{"属性值",new {dataformat=0,width=10}}
};
var sheet2 = workbook.CreateSheet(moreInfoSheetName);
var morerowFirst = sheet2.CreateRow(0);
int moreCellIndex = 0;
foreach (var key in moreheaderLabels.Keys)
{
var cell = morerowFirst.CreateCell(moreCellIndex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(key);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
moreCellIndex++;
}
for (int i = 3; i <= 11; i++)
{
var cell = morerowFirst.CreateCell(i);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(string.Empty);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(i, 10 * 255);
}
//set merge cell for last cell
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
//set constraint
DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
codedataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(codedataValidation);
for (int i = 1; i < 1000; i++)
{
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
new CellRangeAddress(i,i,12,12));
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
new CellRangeAddress(i, i, 13, 13));
sheet2.SetColumnHidden(12, true);
sheet2.SetColumnHidden(13, true);
DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
pdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pdataValidation);
//set constraint
DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
pvdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pvdataValidation);
}
workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));
var ms = new MemoryStream();
workbook.Write(ms);
ms.Position = 0;
var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
return File(ms, "application/vnd.ms-excel", downloadName);
}
/// <summary>
/// 生成下拉列表
/// 此方法暂时不能使用
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="FirstCol"></param>
/// <param name="LastCol"></param>
/// <param name="arr"></param>
public void WriteDropDownList2(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] data)
{
string tmpShtDictionary = "tmpShtDictionary";
Sheet tmpSheet = _workbook.CreateSheet(tmpShtDictionary);
for (int i = 0; i < data.Length; i++)
{
tmpSheet.CreateRow(i).CreateCell(0).SetCellValue(data[i]);
}
Name range = _workbook.CreateName();
range.RefersToFormula = tmpShtDictionary + "!$A1:$A" + data.Length.ToString();
range.NameName = "dicRange";
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)sheet).AddValidationData(dataValidation);
_workbook.RemoveSheetAt(_workbook.GetSheetIndex(tmpSheet));
_workbook.RemoveName(range.NameName);
}
/// <summary>
/// 生成下拉列表
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="FirstCol"></param>
/// <param name="LastRow"></param>
/// <param name="arr"></param>
public void WriteDropDownList(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] arr)
{
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(arr);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)sheet).AddValidationData(dataValidation);
}
//数据有效性以及下拉框的设置
private static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo)
{
CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index);
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
IEnumerable<IDecoderItem> data = GetDecoderItem(fieldInfo);
if (data != null)
{
List<string> optionList = new List<string>();
foreach (IDecoderItem item in data)
{
if (item != null)
{
TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null);
optionList.Add(item.Name);
}
}
constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray());
}
}
else
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", "" });
}
else
{
switch (fieldInfo.DataType)
{
case TkDataType.DateTime:
case TkDataType.Date:
constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd");
break;
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0");
break;
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Bit:
constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0");
break;
default:
break;
}
}
}
if (constraint != null)
{
dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", "You must input a valid value!");
}
return dataValidate;
}
//#region 设计Excel模板
//public static byte[] GetTemplate(Tk5ListMetaData metaInfos)
//{
// MemoryStream ms = new MemoryStream();
// using (ms)
// {
// HSSFWorkbook workbook = new HSSFWorkbook();
// HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaInfos.Table.TableDesc);
// HSSFRow row = (HSSFRow)sheet.CreateRow(0);
// int index = 0;
// foreach(Tk5FieldInfoEx meta)
// }
//}
//#endregion
#region 相关的方法2
//public static void InitializeWorkbook(HSSFWorkbook workbook)
//{
// ////create a entry of DocumentSummaryInformation
// if (workbook == null)
// workbook = new HSSFWorkbook();
// //HSSFFont font1 = workbook.CreateFont();
// //HSSFCellStyle Style = workbook.CreateCellStyle();
// //font1.FontHeightInPoints = 10;
// //font1.FontName = "新細明體";
// //Style.SetFont(font1);
// //for (int i = 0; i < workbook.NumberOfSheets; i++)
// //{
// // HSSFSheet Sheets = workbook.GetSheetAt(0);
// // for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++)
// // {
// // HSSFRow row = Sheets.GetRow(k);
// // for (int l = row.FirstCellNum; l < row.LastCellNum; l++)
// // {
// // HSSFCell Cell = row.GetCell(l);
// // Cell.CellStyle = Style;
// // }
// // }
// //}
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "測試公司";
// workbook.DocumentSummaryInformation = dsi;
// ////create a entry of SummaryInformation
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Subject = "測試公司Excel檔案";
// si.Title = "測試公司Excel檔案";
// si.Author = "killysss";
// si.Comments = "謝謝您的使用！";
// workbook.SummaryInformation = si;
//}
////对象保存在流中，可以通过以下方法输出到浏览器，或是保存到硬盘中:
//public void SaveToFile(MemoryStream ms, string fileName)
//{
// using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
// {
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// data = null;
// }
//}
////public void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
////{
//// if (context.Request.Browser.Browser == "IE")
//// fileName = HttpUtility.UrlEncode(fileName);
//// context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
//// context.Response.BinaryWrite(ms.ToArray());
////}
#endregion
#region 导出Excel
public static void ExportEasy(string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
//ISheet sheet2 = workbook.CreateSheet("ShtDirectionary");
//sheet2.CreateRow(0).CreateCell(0).SetCellValue("Red");
//sheet2.CreateRow(1).CreateCell(0).SetCellValue("Yellow");
//sheet2.CreateRow(2).CreateCell(0).SetCellValue("Black");
//sheet2.CreateRow(3).CreateCell(0).SetCellValue("White");
//sheet2.CreateRow(4).CreateCell(0).SetCellValue("Green");
//IName range = workbook.CreateName();
//range.RefersToFormula = "ShtDirectionary!$A1:A4";
//range.NameName = "dicRange";
ISheet sheet = workbook.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell1 = row.CreateCell(0);
ICell cell2 = row.CreateCell(1);
ICell cell3 = row.CreateCell(2);
IDataFormat format = workbook.CreateDataFormat();
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
sheet.SetDefaultColumnStyle(1, cellStyle1);
cell1.SetCellValue("日期列");
cell2.SetCellValue("数值列");
cell3.SetCellValue("总公司");
//CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 0, 0);
////DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "Red", "Yellow", "Black" });
//HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
//((HSSFSheet)sheet).AddValidationData(dataValidate1);
CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535, 0, 0);
CellRangeAddressList regions2 = new CellRangeAddressList(1, 65535, 1, 1);
CellRangeAddressList regions3 = new CellRangeAddressList(1, 65535, 2, 2);
// string formulation = "left(right(A1,3),1)=\".\"";
// string formula = "LEFT(RIGHT(A6,3),1)=\".\"";
// DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", " " });
DVConstraint constraint1 = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd");
DVConstraint constraint2 = DVConstraint.CreateNumericConstraint(6, 6, "0", null);
DVConstraint constraint3 = DVConstraint.CreateExplicitListConstraint(new string[] { "√"});
HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1);
dataValidate1.CreateErrorBox("error", "You must input a date");
// dataValidate1.EmptyCellAllowed = false;
HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2);
dataValidate2.CreateErrorBox("error", "You must input a valid value!");
HSSFDataValidation dataValidate3 = new HSSFDataValidation(regions3, constraint3);
dataValidate3.CreateErrorBox("error", "You must input a valid value!");
((HSSFSheet)sheet).AddValidationData(dataValidate2);
((HSSFSheet)sheet).AddValidationData(dataValidate1);
((HSSFSheet)sheet).AddValidationData(dataValidate3);
#region 已注释内容（作为参考）
//cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//cellStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle1.ShrinkToFit = true;
//ICellStyle cellStyle2 = workbook.CreateCellStyle();
//cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//cellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle2.ShrinkToFit = true;
//ICellStyle cellStyle3 = workbook.CreateCellStyle();
//cellStyle3.CloneStyleFrom(cellStyle2);
//cellStyle3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//ICellStyle cellStyle4 = workbook.CreateCellStyle();
//cellStyle4.CloneStyleFrom(cellStyle2);
//cellStyle4.DataFormat = format.GetFormat("@");
//ICellStyle cellStyle5 = workbook.CreateCellStyle();
//cellStyle5.CloneStyleFrom(cellStyle4);
//cellStyle5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//// InitializeWorkbook(workbook);
//sheet.SetColumnWidth(0, 15 * 256);
//sheet.SetColumnWidth(1, 10 * 256);
//sheet.SetColumnWidth(2, 19 * 256);
//sheet.Header.Center = "zll NPOI Test";
//sheet.Footer.Left = "Copyright mitu Team";
//sheet.Footer.Right = "created by 2015";
//ICellStyle style1 = workbook.CreateCellStyle();
//// 设置字体
//IFont font1 = workbook.CreateFont();
//font1.FontName = "黑体";
//font1.FontHeightInPoints = 12;
//font1.Color = NPOI.HSSF.Util.HSSFColor.Green.Index;
//style1.SetFont(font1);
////设置对其方式
//style1.Alignment = HorizontalAlignment.Center;
//style1.VerticalAlignment = VerticalAlignment.Center;
//style1.WrapText = true;
//ICellStyle cellStyle = workbook.CreateCellStyle();
//cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
////设置页眉页脚，打印预览
//sheet.Header.Center = "This is a test sheet";
//sheet.Footer.Left = "Copyright NPOI Team";
//sheet.Footer.Right = "created by 2015-01-20";
////填充表头
//IRow dataRow = sheet.CreateRow(0);
//dataRow.HeightInPoints = 30;
//foreach (DataColumn column in dtSource.Columns)
//{
// sheet.SetColumnWidth(0, 10 * 256);
// ICell cell = dataRow.CreateCell(column.Ordinal);
// cell.CellStyle = style1;
// cell.SetCellValue(column.ColumnName);
//}
////填充内容
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// dataRow = sheet.CreateRow(i + 1);
// for (int j = 0; j < dtSource.Columns.Count; j++)
// {
// ICell cell = dataRow.CreateCell(j);
// cell.SetCellValue(Convert.ToDateTime(dtSource.Rows[i][j]));
// cell.CellStyle = cellStyle;
// }
//}
#endregion
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
/// <summary>
/// Creates a data validation object
/// </summary>
/// <param name="dataValidation">The Data validation object settings</param>
public void AddValidationData(HSSFDataValidation dataValidation)
{
if (dataValidation == null)
{
throw new ArgumentException("objValidation must not be null");
}
DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable();
DVRecord dvRecord = dataValidation.CreateDVRecord(_workbook);
dvt.AddDataValidation(dvRecord);
}
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region,
DVConstraint constraint, string errorMsg)
{
HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", errorMsg);
return dataValidate;
}
