C# NPOI使用

  1 HSSFWorkbook workbook = new HSSFWorkbook();
  2 HSSFSheet sheet = workbook.CreateSheet( "Sheet1") as HSSFSheet;
  3 IRow row = sheet.CreateRow(0);
  4     row.Height = 30 * 20;
  5 
  6 ICell cellTitle = row.CreateCell(0);
  7     titleHeader.Alignment = HorizontalAlignment.Center;
  8     titleHeader.VerticalAlignment = VerticalAlignment.Center;
  9     
 10         style.BorderBottom = BorderStyle.Thin;
 11         style.BorderLeft = BorderStyle.Thin;
 12         style.BorderRight = BorderStyle.Thin;
 13 
 14 IFont font = workbook.CreateFont();
 15     font.FontHeightInPoints = 14;
 16     font.FontName = "微软雅黑";
 17         font.IsBold = true;
 18 
 19 cellTitle.SetFont(font);
 20 cellTitle.SetCellValue(titleName)
 21 
 22 
 23 Color c = Color.FromArgb(215, 228, 188);
 24                 HSSFPalette palette = workbook.GetCustomPalette();
 25                 palette.SetColorAtIndex((short)63, c.R, c.G, c.B);
 26                 HSSFColor cellColor = palette.FindColor(c.R, c.G, c.B);
 27 style.FillPattern = FillPattern.SolidForeground;
 28 style.FillForegroundColor = cellColor.Indexed;
 29 
 30 region = new CellRangeAddress(3, 3, 15, columnsCount - 1);
 31 sheet.AddMergedRegion(region);
 32 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, HSSFColor.Black.Index);
 33 
 34 
 35 
 36    //列宽自适应，只对英文和数字有效
 37                 for (int i = 0; i <= columnsCount; i++)
 38                 {
 39                     sheet.AutoSizeColumn(i);
 40                 }
 41 
 42                 //列宽自适应中文有效
 43                 for (int i = 0; i < 15; i++)
 44                 {
 45                     int columnWidth = sheet.GetColumnWidth(i) / 256;
 46                     for (int rowNum = 4; rowNum < 6 + rowsCount; rowNum++)
 47                     {
 48                         IRow currentRow;
 49                         //当前行未被使用过
 50                         if (sheet.GetRow(rowNum) == null)
 51                         {
 52                             currentRow = sheet.CreateRow(rowNum);
 53                         }
 54                         else
 55                         {
 56                             currentRow = sheet.GetRow(rowNum);
 57                         }
 58 
 59                         if (currentRow.GetCell(i) != null)
 60                         {
 61                             ICell currentCell = currentRow.GetCell(i);
 62                             int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
 63                             if (columnWidth < length)
 64                             {
 65                                 columnWidth = length;
 66                             }
 67                         }
 68                     }
 69                     sheet.SetColumnWidth(i, columnWidth * 350);
 70                 }
 71 
 72                 //列宽自适应中文有效
 73                 for (int i = 15; i < columnsCount; i++)
 74                 {
 75                     int rowNum;
 76 
 77                     if (dtSource.Columns[i].ColumnName.Contains("/"))
 78                     {
 79                         rowNum = 4;
 80                     }
 81                     else
 82                     {
 83                         rowNum = 5;
 84                     }
 85 
 86                     int columnWidth = sheet.GetColumnWidth(i) / 256;
 87                     for (; rowNum < 6 + rowsCount; rowNum++)
 88                     {
 89                         IRow currentRow;
 90                         //当前行未被使用过
 91                         if (sheet.GetRow(rowNum) == null)
 92                         {
 93                             currentRow = sheet.CreateRow(rowNum);
 94                         }
 95                         else
 96                         {
 97                             currentRow = sheet.GetRow(rowNum);
 98                         }
 99 
100                         if (currentRow.GetCell(i) != null)
101                         {
102                             ICell currentCell = currentRow.GetCell(i);
103                             int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
104                             if (columnWidth < length)
105                             {
106                                 columnWidth = length;
107                             }
108                         }
109                     }
110                     sheet.SetColumnWidth(i, columnWidth * 350);
111                 }
112 
113 
114  //若没有数据则建立空文档
115             if (workbook.NumberOfSheets == 0)
116             {
117                 HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
118             }
119 
120   //写文件
121             MemoryStream ms = new MemoryStream();
122             workbook.Write(ms);
123             ms.Flush();
124             ms.Seek(0, SeekOrigin.Begin); //ms.Position = 0;
125 
126     return ms;
127 
128    if (j == 14 )
129     {
130     double db = 0;
131     if (double.TryParse(objVal.ToString(), out db))
132     {
133         cell.SetCellValue(db);
134     }
135     }
136     else
137     {
138     SetCellValue(cell, objVal);
139     }
140 
141 public static void SetCellValue(ICell eCell, object data)
142 {
143     string typeStr = data.GetType().ToString();
144 
145     switch (typeStr)
146     {
147         case "System.String":
148             eCell.SetCellValue(data.ToString());
149             break;
150         case "System.DateTime":
151             System.DateTime dateV;
152             System.DateTime.TryParse(data.ToString(), out dateV);
153             eCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));
154             break;
155         case "System.Boolean":
156             bool boolV = false;
157             bool.TryParse(data.ToString(), out boolV);
158             eCell.SetCellValue(boolV);
159             break;
160         case "System.Int16":
161         case "System.Int32":
162         case "System.Int64":
163         case "System.Byte":
164             int intV = 0;
165             int.TryParse(data.ToString(), out intV);
166             eCell.SetCellValue(intV);
167             break;
168         case "System.Decimal":
169         case "System.Double":
170             double doubV = 0;
171             double.TryParse(data.ToString(), out doubV);
172             eCell.SetCellValue(doubV);
173             break;
174         case "System.DBNull":
175             eCell.SetCellValue("");
176             break;
177         default:
178             eCell.SetCellValue("");
179             break;
180     }
181 }

 

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook. CreateSheet( "Sheet1") as HSSFSheet;

IRow row = sheet. CreateRow( 0);

     row. Height = 30 * 20;

ICell cellTitle = row. CreateCell( 0);

     titleHeader. Alignment = HorizontalAlignment. Center;

     titleHeader. VerticalAlignment = VerticalAlignment. Center;

    

style. BorderBottom = BorderStyle. Thin;

style. BorderLeft = BorderStyle. Thin;

style. BorderRight = BorderStyle. Thin;

IFont font = workbook. CreateFont();

     font. FontHeightInPoints = 14;

     font. FontName = "微软雅黑";

font. IsBold = true;

cellTitle. SetFont( font);

cellTitle. SetCellValue( titleName)

Color c = Color. FromArgb( 215, 228, 188);

HSSFPalette palette = workbook. GetCustomPalette();

palette. SetColorAtIndex(( short) 63, c. R, c. G, c. B);

HSSFColor cellColor = palette. FindColor( c. R, c. G, c. B);

style. FillPattern = FillPattern. SolidForeground;

style. FillForegroundColor = cellColor. Indexed;

region = new CellRangeAddress( 3, 3, 15, columnsCount - 1);

sheet. AddMergedRegion( region);

(( HSSFSheet) sheet). SetEnclosedBorderOfRegion( region, BorderStyle. Thin, HSSFColor. Black. Index);

//列宽自适应，只对英文和数字有效

for ( int i = 0; i <= columnsCount; i++)

{

sheet. AutoSizeColumn( i);

}

//列宽自适应中文有效

for ( int i = 0; i < 15; i++)

{

int columnWidth = sheet. GetColumnWidth( i) / 256;

for ( int rowNum = 4; rowNum < 6 + rowsCount; rowNum++)

{

IRow currentRow;

//当前行未被使用过

if ( sheet. GetRow( rowNum) == null)

{

currentRow = sheet. CreateRow( rowNum);

}

else

{

currentRow = sheet. GetRow( rowNum);

}

if ( currentRow. GetCell( i) != null)

{

ICell currentCell = currentRow. GetCell( i);

int length = Encoding. Default. GetBytes( currentCell. ToString()). Length;

if ( columnWidth < length)

{

columnWidth = length;

}

}

}

sheet. SetColumnWidth( i, columnWidth * 350);

}

//列宽自适应中文有效

for ( int i = 15; i < columnsCount; i++)

{

int rowNum;

if ( dtSource. Columns[ i]. ColumnName. Contains( "/"))

{

rowNum = 4;

}

else

{

rowNum = 5;

}

int columnWidth = sheet. GetColumnWidth( i) / 256;

for (; rowNum < 6 + rowsCount; rowNum++)

{

IRow currentRow;

//当前行未被使用过

if ( sheet. GetRow( rowNum) == null)

{

currentRow = sheet. CreateRow( rowNum);

}

else

{

currentRow = sheet. GetRow( rowNum);

}

if ( currentRow. GetCell( i) != null)

{

ICell currentCell = currentRow. GetCell( i);

int length = Encoding. Default. GetBytes( currentCell. ToString()). Length;

if ( columnWidth < length)

{

columnWidth = length;

}

}

}

sheet. SetColumnWidth( i, columnWidth * 350);

}

//若没有数据则建立空文档

if ( workbook. NumberOfSheets == 0)

{

HSSFSheet sheet = workbook. CreateSheet( "Sheet1") as HSSFSheet;

}

//写文件

MemoryStream ms = new MemoryStream();

workbook. Write( ms);

ms. Flush();

ms. Seek( 0, SeekOrigin. Begin); //ms.Position = 0;

return ms;

if ( j == 14 )

{

double db = 0;

if ( double. TryParse( objVal. ToString(), out db))

{

cell. SetCellValue( db);

}

}

else

{

SetCellValue( cell, objVal);

}

public static void SetCellValue( ICell eCell, object data)

{

string typeStr = data. GetType(). ToString();

switch ( typeStr)

{

case "System.String":

eCell. SetCellValue( data. ToString());

break;

case "System.DateTime":

System. DateTime dateV;

System. DateTime. TryParse( data. ToString(), out dateV);

eCell. SetCellValue( dateV. ToString( "yyyy/MM/dd"));

break;

case "System.Boolean":

bool boolV = false;

bool. TryParse( data. ToString(), out boolV);

eCell. SetCellValue( boolV);

break;

case "System.Int16":

case "System.Int32":

case "System.Int64":

case "System.Byte":

int intV = 0;

int. TryParse( data. ToString(), out intV);

eCell. SetCellValue( intV);

break;

case "System.Decimal":

case "System.Double":

double doubV = 0;

double. TryParse( data. ToString(), out doubV);

eCell. SetCellValue( doubV);

break;

case "System.DBNull":

eCell. SetCellValue( "");

break;

default:

eCell. SetCellValue( "");

break;

}

}

转载于:https://www.cnblogs.com/LiuFengH/p/9876036.html