<转>Npoi导入导出Excel操作<载>

复制代码
 1 //Datatable导出Excel
 2 private static void GridToExcelByNPOI(DataTable dt, string strExcelFileName)
 3         {
 4             try
 5             {
 6 HSSFWorkbook workbook = new HSSFWorkbook(); 
 7                 ISheet sheet = workbook.CreateSheet("Sheet1");
 8 
 9                 ICellStyle HeadercellStyle = workbook.CreateCellStyle();
10                 HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
11                 HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
12                 HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
13                 HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
14                 HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
15                 //字体
16                 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
17                 headerfont.Boldweight = (short)FontBoldWeight.Bold;
18                 HeadercellStyle.SetFont(headerfont);
19 
20 
21                 //用column name 作为列名
22                 int icolIndex = 0;
23                 IRow headerRow = sheet.CreateRow(0);
24                 foreach (DataColumn item in dt.Columns)
25                 {
26                     ICell cell = headerRow.CreateCell(icolIndex);
27                     cell.SetCellValue(item.ColumnName);
28                     cell.CellStyle = HeadercellStyle;
29                     icolIndex++;
30                 }
31 
32                 ICellStyle cellStyle = workbook.CreateCellStyle();
33 
34                 //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
35                 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
36                 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
37                 cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
38                 cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
39                 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
40 
41 
42                 NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
43                 cellfont.Boldweight = (short)FontBoldWeight.Normal;
44                 cellStyle.SetFont(cellfont);
45 
46                 //建立内容行
47                 int iRowIndex = 1;
48                 int iCellIndex = 0;
49                 foreach (DataRow Rowitem in dt.Rows)
50                 {
51                     IRow DataRow = sheet.CreateRow(iRowIndex);
52                     foreach (DataColumn Colitem in dt.Columns)
53                     {
54 
55                         ICell cell = DataRow.CreateCell(iCellIndex);
56                         cell.SetCellValue(Rowitem[Colitem].ToString());
57                         cell.CellStyle = cellStyle;
58                         iCellIndex++;
59                     }
60                     iCellIndex = 0;
61                     iRowIndex++;
62                 }
63 
64                 //自适应列宽度
65                 for (int i = 0; i < icolIndex; i++)
66                 {
67                     sheet.AutoSizeColumn(i);
68                 }
69 
70                 //写Excel
71                 FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
72                 workbook.Write(file);
73                 file.Flush();
74                 file.Close();
75 
76                 MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
77             }
78             catch (Exception ex)
79             {
80                 ILog log = LogManager.GetLogger("Exception Log");
81                 log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
82                 //记录AuditTrail
83                 CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);
84 
85                 MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
86             }
87             finally { workbook = null; }
88 
89         }
复制代码

*********************************

复制代码
/// <summary>
        /// Excel文件导成Datatable
        /// </summary>
        /// <param name="strFilePath">Excel文件目录地址</param>
        /// <param name="strTableName">Datatable表名</param>
        /// <param name="iSheetIndex">Excel sheet index</param>
        /// <returns></returns>
        public static DataTable XlSToDataTable(string strFilePath, string strTableName,int iSheetIndex)
        {

            string strExtName = Path.GetExtension(strFilePath);

            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(strTableName))
            {
                dt.TableName = strTableName;
            }

            if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(file);
                    ISheet sheet = workbook.GetSheetAt(iSheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(),typeof(string));
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    while(rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
           }

            return dt;
        }
复制代码

。。。

转载别人的；

自己做个记录；

以后万一再用到了呢···

 

 

下面是另外的一份···可能版本不同吧···

复制代码
        private void ExportExcel_NOPI(DataTable table_data)
        {


            Dictionary<string, string> enToZh = new Dictionary<string, string>();//dataTable中需要转换的值<需要将key即datatable中的值转换为中文值>
            enToZh.Add("ID", "序号");

            enToZh.Add("LOGINDAYS", "总天数");

            table_data.Columns.RemoveAt(2);
            table_data.Columns.RemoveAt(3);//先要移除不需要的列
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Excel");

            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            ICellStyle stringStyle = hssfworkbook.CreateCellStyle();
            stringStyle.VerticalAlignment = VerticalAlignment.Center;

            //取得列宽
            int columnCount = table_data.Columns.Count;
            int[] arrColWidth = new int[columnCount];
            int width = 8;
            foreach (DataColumn column in table_data.Columns)
            {
                arrColWidth[column.Ordinal] = width;
            }

            int rowIndex = 0;
            string temp_col1 = "";
            int col1_s = 0;

            foreach (DataRow row in table_data.Rows)
            {


                #region 新建表，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet1 = hssfworkbook.CreateSheet();
                    }

                    #region 列头及样式

                    IRow headerRow = sheet1.CreateRow(0);
                    //240,255,255
                    ICellStyle headStyle = hssfworkbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    headStyle.VerticalAlignment = VerticalAlignment.Center;

                    headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//将标题颜色设置成灰色
                    headStyle.FillPattern = FillPattern.SolidForeground;//FillPattern 为单元格背景色的填充样式
                    IFont font = hssfworkbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);

                    foreach (DataColumn column in table_data.Columns)
                    {

                        string columName = column.ColumnName;
                        if (enToZh.ContainsKey(columName))
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(enToZh[columName]);
                        }
                        else
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        }
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        sheet1.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//设置列宽

                    }

                    #endregion

                    rowIndex = 1;
                }
                #endregion


                #region 填充内容

                int j = 1;
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in table_data.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.CellStyle = stringStyle;
                            if (j == 1)
                            {
                                newCell.SetCellValue(rowIndex);
                            }
                            else if (drValue == "1" && j > 3)//如果是"1",将单元格颜色变色
                            {
                                ICellStyle tempStyle = hssfworkbook.CreateCellStyle();
                                tempStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
                                tempStyle.FillPattern = FillPattern.SolidForeground;
                                newCell.CellStyle = tempStyle;
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }
                            break;
                        case "System.Int32"://字符串类型
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = stringStyle;
                            break;
                        case "System.Double":
                            if (drValue != "")
                            {
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                            }
                            else
                            {
                                newCell.SetCellValue("");
                            }
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                    #region 单元格合并（这里只合并第一列）

                    if (j == 1 && temp_col1 != drValue)
                    {
                        if (temp_col1 != "")
                        {
                            sheet1.AddMergedRegion(new CellRangeAddress(col1_s, rowIndex - 1, 0, 0));
                        }
                        temp_col1 = drValue;
                        col1_s = rowIndex;
                    }

                    #endregion

                    j++;
                }
                #endregion

                rowIndex++;
            }

            //冻结窗口  锁定表头和第一列
            sheet1.CreateFreezePane(1, 1, 1, 1);

            //输出

            Context.Response.ContentType = "application/vnd.ms-excel";
            Context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Excel.xls"));
            Context.Response.Clear();

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            file.WriteTo(Context.Response.OutputStream);
            Context.Response.End();
        }
复制代码

 

posted @   zh89233  阅读(568)  评论(0)  编辑  收藏  举报
编辑推荐：
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示