增加数据格式
public static void TableToExcel2(DataTable table, string filename, string sheetname)
{
XSSFWorkbook workbook = new XSSFWorkbook();
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 9;
IFont fonthader = workbook.CreateFont();
fonthader.FontName = "微软雅黑";
fonthader.FontHeightInPoints = 9;
fonthader.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font);
ICellStyle stylehead = workbook.CreateCellStyle();
stylehead.SetFont(fonthader);
var sheet = workbook.CreateSheet(sheetname);
var headerRow = sheet.CreateRow(0);
//head
foreach (DataColumn column in table.Columns)
{
var cellhead = headerRow.CreateCell(column.Ordinal);//
cellhead.CellStyle = stylehead;
cellhead.SetCellValue(column.Caption);
}
//设置大类限制
ArrayList cat1 = new ArrayList();
DataSet ds = DBHelper.Instance.ExeDataSet(" select catname from purchase_categories where iscat=1 and parentid=0 ");
foreach (DataRow dr in ds.Tables[0].Rows)
{
cat1.Add(dr["catname"]);
}
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
NPOI.SS.Util.CellRangeAddressList regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 0, 0);
IDataValidationConstraint constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
IDataValidation validation = dvHelper.CreateValidation(constraint, regCat1);
sheet.AddValidationData(validation);
//小类
regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 1, 1);
cat1 = new ArrayList();
ds = DBHelper.Instance.ExeDataSet(" select catname from purchase_categories where iscat=1 and parentid!=0 ");
foreach (DataRow dr in ds.Tables[0].Rows)
{
cat1.Add(dr["catname"]);
}
constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
validation = dvHelper.CreateValidation(constraint, regCat1);
sheet.AddValidationData(validation);
单位
regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 6, 6);
cat1 = new ArrayList();
DataView dv = Purchase.BLL.PurchaseHelper.GetParam("unit");
foreach (DataRowView dvr in dv)
{
cat1.Add(dvr["paramname"]);
}
constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
validation = dvHelper.CreateValidation(constraint, regCat1);
sheet.AddValidationData(validation);
货币单位
regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 65535, 8, 8);
cat1 = new ArrayList();
dv = Purchase.BLL.PurchaseHelper.GetParam("currency");
foreach (DataRowView dvr in dv)
{
cat1.Add(dvr["paramname"]);
}
constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
validation = dvHelper.CreateValidation(constraint, regCat1);
sheet.AddValidationData(validation);
//货币单位
regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 65535, 11, 11);
cat1 = new ArrayList();
dv = Purchase.BLL.PurchaseHelper.GetParam("shipmethod");
foreach (DataRowView dvr in dv)
{
cat1.Add(dvr["paramname"]);
}
constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
validation = dvHelper.CreateValidation(constraint, regCat1);
sheet.AddValidationData(validation);
//body
var rowIndex = 1;
foreach (DataRow row in table.Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 20;
foreach (DataColumn column in table.Columns)
{
ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = style1;
}
rowIndex++;
}
FileStream sw = File.Create(HttpContext.Current.Server.MapPath(filename));
workbook.Write(sw);
sw.Close();
}