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;
    }
}