• 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;
    }
    }
  • 相关阅读:
    2.GO-可变参数函数、匿名函数和函数变量
    1.Go-copy函数、sort排序、双向链表、list操作和双向循环链表
    第四章、Go-面向“对象”
    第三章、Go-内建容器
    第二章、Go-基础语法
    第一章、Go安装与Goland破解
    arthas使用分享
    redis如何后台启动
    java.io.IOException: Could not locate executable nullinwinutils.exe in the Hadoop binaries
    安装redis出现cc adlist.o /bin/sh:1:cc:not found的解决方法
  • 原文地址：https://www.cnblogs.com/LiuFengH/p/9876036.html
Copyright © 2020-2023  润新知