• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#NPOI使用

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
  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;
}
}

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#WPF简单自定义菜单切换动画发布时间:2022-07-14
下一篇:
C#破解防盗链发布时间:2022-07-14
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap