//导出数据
protected void btnModel_Click(object sender, EventArgs e)
{
CreateExcelTemp();
string path = this.MapPath("~/BaseInfo/Temp/GABProdectInfoMaintain.xls");
ExcelOutE(this, path); //此方法在NOPI导出导入数据中
}
private void CreateExcelTemp()
{
HSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("Sheet xls");
HSSFSheet hidden = (HSSFSheet)workbook.CreateSheet("hidden");
string[] pgName = GetDataStr();
for (int i = 0, length = pgName.Length; i < length; i++)
{
string name = pgName[i];
HSSFRow row = (HSSFRow)hidden.CreateRow(i);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue(name);
}
IRow row0 = sheet1.CreateRow(0);
row0.CreateCell(0).SetCellValue("GAB其他产品信息维护");
IRow row1 = sheet1.CreateRow(1);
row1.CreateCell(0).SetCellValue("产品组");
row1.CreateCell(1).SetCellValue("BOM编号");
row1.CreateCell(2).SetCellValue("产品名称");
row1.CreateCell(3).SetCellValue("产品价格");
IName namedCell = workbook.CreateName();
namedCell.NameName = "hidden";
namedCell.RefersToFormula = "hidden!$A$1:$A$" + pgName.Length;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("hidden");
//引用hidden中的数据(开始行,结束行,开始列,结束列)
CellRangeAddressList addressList = new CellRangeAddressList(2, 65535, 0, 0);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
workbook.SetSheetHidden(1, true);
sheet1.AddValidationData(validation);
//添加格式(数字的字符串格式)
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("@");
sheet1.SetDefaultColumnStyle(0, cellStyle);
sheet1.SetDefaultColumnStyle(1, cellStyle);
sheet1.SetDefaultColumnStyle(2, cellStyle);
sheet1.SetColumnWidth(0, 8000);
sheet1.SetColumnWidth(1, 5000);
sheet1.SetColumnWidth(2, 20000);
sheet1.SetColumnWidth(3, 5000);
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
DataTable dt = A_GABCustomerRelationBusiness.GetGABProductInfo();
if (dt != null || dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
HSSFRow rowtemp = (HSSFRow)sheet1.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]["BOMCode"].ToString());
ICell celltemp2 = rowtemp.CreateCell(2);
celltemp2.SetCellValue(dt.Rows[i]["ProductName"].ToString());
ICell celltemp3 = rowtemp.CreateCell(3);
celltemp3.SetCellValue(dt.Rows[i]["ProductPrice"].ToString());
}
}
//保存修改的模板
string savePath = this.MapPath("~/BaseInfo/Temp/GABProdectInfoMaintain.xls");
using (FileStream file = new FileStream(savePath, FileMode.Create))
{
workbook.Write(file);
}
}
private string[] GetDataStr()
{
List<string> DataList = new List<string>();
DataList.Add("ThinkCentre M及启天产品");
DataList.Add("笔记本昭阳");
DataList.Add("LCS 新技术孵化器");
DataList.Add("LCS ThinkPad CTO");
DataList.Add("ThinkPad");
DataList.Add("Think KAB");
DataList.Add("LCS ThinkStation CTO");
DataList.Add("ThinkStation");
DataList.Add("Options");
DataList.Add("服务器存储");
DataList.Add("高性能服务器");
DataList.Add("企业级存储-NAS");
DataList.Add("企业级存储-SAN");
DataList.Add("乐Pad");
DataList.Add("行业平板电脑");
DataList.Add("LCS 联想打印机");
DataList.Add("LCS 激光打印机耗材");
DataList.Add("大客户服务");
DataList.Add("LCS 移动存储");
DataList.Add("消费台式电脑");
DataList.Add("LCS 软件");
DataList.Add("LCS 信息服务");
DataList.Add("LCS 存储产品");
DataList.Add("LCS 外设喷墨打印机及耗材");
DataList.Add("LCS IT 1for1教育软件");
DataList.Add("信息安全产品");
DataList.Add("LCS IT 1FOR1");
DataList.Add("LCS 网络产品");
DataList.Add("PC Option");
DataList.Add("产品服务部");
DataList.Add("LCS 服务产品");
DataList.Add("LCS 高端服务器");
DataList.Add("IdeaCentre Option");
DataList.Add("数码维修备件");
DataList.Add("数码影音");
DataList.Add("LCS 存储卡");
DataList.Add("LCS 消费笔记本(旧)");
DataList.Add("商用台式电脑扬天产品");
DataList.Add("LCS BBPC");
DataList.Add("LCS 外设投影机");
DataList.Add("IdeaPad Option");
DataList.Add("LCS无线应用");
DataList.Add("笔记本电脑天逸旭日产品");
DataList.Add("LCS CDB配售业务");
DataList.Add("LCS 随身电脑");
DataList.Add("007产品");
DataList.Add("乐Phone");
DataList.Add("LCS QDI 中国区");
return DataList.ToArray();
}