nopi设置excel单元格的格式汇总

public void ModifySheetCellFormat()

{

#region 打开Excel表格模板，并初始化到NPOI对象中

IWorkbook wk = null;

string extension = System.IO.Path.GetExtension(sExcelFilename);

FileStream fs = File.OpenRead(sExcelFilename);

if (extension.Equals(".xls"))

wk = new HSSFWorkbook(fs);

else

wk = new XSSFWorkbook(fs);

fs.Close();

#endregion

//List<string> SheetNames = new List<string>();

//for (int i = 0; i < wk.NumberOfSheets; i++)

//{

// SheetNames.Add(wk.GetSheetName(i));

//}

string sheetname = SelectedSheetName.Substring(1, SelectedSheetName.Length - 3);

ISheet sheet = wk.GetSheet(sheetname);

IRow row = null;//数据行

ICell cell = null;//数据行中的某列

for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)

{

row = sheet.GetRow(i);

cell = row.GetCell(1);

IDataFormat dataformat = wk.CreateDataFormat();

ICellStyle style0 = wk.CreateCellStyle();

style0.DataFormat = dataformat.GetFormat("@");//转化为汉字大写

cell.CellStyle = style0;

cell = row.GetCell(52);

IDataFormat dataformat2 = wk.CreateDataFormat();

ICellStyle style1 = wk.CreateCellStyle();

style1.DataFormat = dataformat.GetFormat("@");//转化为汉字大写

cell.CellStyle = style1;

}

FileStream file = new FileStream(sExcelFilename, FileMode.Create);

wk.Write(file);

file.Close();

}

IDataFormat dataformat = wk.CreateDataFormat();

ICellStyle style0 = wk.CreateCellStyle();

style0.DataFormat = dataformat.GetFormat("[DbNum2][$-804]General");//转化为汉字大写

ICellStyle style1 = wk.CreateCellStyle();

style1.DataFormat = dataformat.GetFormat("0.0"); //改变小数精度【小数点后有几个0表示精确到小数点后几位】

ICellStyle style2 = wk.CreateCellStyle();

style2.DataFormat = dataformat.GetFormat("#,##0.0");//分段添加，号

ICellStyle style3 = wk.CreateCellStyle();

style3.DataFormat = dataformat.GetFormat("0.00E+00");//科学计数法

ICellStyle style4 = wk.CreateCellStyle();

style4.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00");//正数与负数的区分

ICellStyle style5 = wk.CreateCellStyle();

style5.DataFormat = dataformat.GetFormat("# ??/??");//整数部分+分数

ICellStyle style6 = wk.CreateCellStyle();

style6.DataFormat = dataformat.GetFormat("??/??");//分数

ICellStyle style7 = wk.CreateCellStyle();

style7.DataFormat = dataformat.GetFormat("0.00%");//百分数【小数点后有几个0表示精确到显示小数点后几位】