程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> NPOI Excel導出數據信息，npoiexcel導出數據

NPOI Excel導出數據信息，npoiexcel導出數據

編輯：關於.NET

NPOI Excel導出數據信息，npoiexcel導出數據

1、這裡引用的是 NPOI1.2.5.0版本。

2、導入命名空間

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

3、新建一個導出類，直接將代碼復制過去即可使用。

#region 通過NPOI組件導出EXCEL
private static HSSFWorkbook hssfworkbook;

        public class CommentModel
        {
            public int rowIndex;
            public int columnIndex;
            public string commentText;

            public CommentModel(int row, int column, string text)
            {
                this.rowIndex = row;
                this.columnIndex = column;
                this.commentText = text;
            }
        }

        public static void NpoiToExcel(DataTable dt)
        {
            NpoiToExcel(dt, null, null, null);
        }

        public static void NpoiToExcel(DataTable dt, List<int> columnsHiddenIndex, List<int> columnsEnableIndex, CommentModel commentModel)
        {
            string filename = string.Format("Excel{0}.xls", System.DateTime.Now.Ticks);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            HttpContext.Current.Response.Clear();
            InitializeWorkbook();
            GenerateData(dt, columnsHiddenIndex, columnsEnableIndex, commentModel);
            HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer());
            HttpContext.Current.Response.End();
        }

        private static MemoryStream WriteToStream()
        {
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file;
        }

        private static void GenerateData(DataTable dt, List<int> columnsHiddenIndex, List<int> columnsEnableIndex, CommentModel commentModel)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

sheet1.DefaultColumnWidth = 20;

IRow rowHeader = sheet1.CreateRow(0);

            // 設置背景顏色為天藍色
            ICellStyle headerCellStyle = hssfworkbook.CreateCellStyle();
            headerCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index;
            headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

            // 設置單元格字體
            IFont font = hssfworkbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.BOLD; // 粗體
            headerCellStyle.SetFont(font);

            // 設置單元格邊框
            headerCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

headerCellStyle.WrapText = true;

            // 設置導出標題
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = rowHeader.CreateCell(i);
                cell.CellStyle = headerCellStyle;
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            // 添加批注
            if (commentModel != null)
            {
                IDrawing patr = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
                HSSFComment comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, commentModel.columnIndex + 1, commentModel.rowIndex + 1, commentModel.columnIndex + 3, commentModel.rowIndex + 6));
                HSSFRichTextString str = new HSSFRichTextString(commentModel.commentText);
                IFont fontComment = hssfworkbook.CreateFont();
                fontComment.FontName = ("Arial");
                fontComment.FontHeightInPoints = 10;
                fontComment.Boldweight = (short)FontBoldWeight.BOLD;
                str.ApplyFont(fontComment);
                comment.String = str;
                comment.Row = commentModel.rowIndex;
                comment.Column = commentModel.columnIndex;
            }

            // 設置單元格邊框
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

            // 設置導出內容
            for (int i = 1; i <= dt.Rows.Count; i++)
            {

IRow row = sheet1.CreateRow(i);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string cellContent = dt.Rows[i - 1][j].ToString();

                    // 判斷字符串大小，最大長度為32767，超過必須截取，否則設置EXCEL單元格會報錯
                    if (cellContent.Length > 32767)
                    {
                        cellContent = cellContent.Substring(0, 32767);

                    }

                    ICell cell = row.CreateCell(j);
                    cell.CellStyle = cellStyle;
                    cell.SetCellType(CellType.STRING);
                    cell.SetCellValue(cellContent);
                }

row.Height = 20 * 20;
}

            if (columnsHiddenIndex != null)
            {
                for (int n = 0; n < columnsHiddenIndex.Count; n++)
                {
                    sheet1.SetColumnHidden(columnsHiddenIndex[n], true);
                }
            }

            if (columnsEnableIndex != null)
            {
                DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "Y", "N" });

                for (int m = 0; m < columnsEnableIndex.Count; m++)
                {
                    //所有序號都從零算起，第一行標題行除外，所以第一個參數是1，65535是一個Sheet的最大行數
                    CellRangeAddressList rangeList = new CellRangeAddressList(1, dt.Rows.Count, columnsEnableIndex[m], columnsEnableIndex[m]);
                    HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
                    ((HSSFSheet)sheet1).AddValidationData(dataValidation);
                }
            }
        }

        private static void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "博客";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "博客園 Export";
            hssfworkbook.SummaryInformation = si;
        }

        /// <summary>
        /// 根據excel文件路徑創建datatable
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string strPath)
        {
            FileStream stream = new FileStream(strPath, FileMode.Open, FileAccess.Read);

DataTable dt = new DataTable();
HSSFWorkbook workbook = new HSSFWorkbook(stream);

            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
            HSSFRow headrow = (HSSFRow)sheet.GetRow(0);
            HSSFCell cell;

            for (int i = 0; i < headrow.LastCellNum; i++)
            {
                cell = (HSSFCell)headrow.GetCell(i);
                dt.Columns.Add(cell.ToString());

}

int herdrowNum = headrow.LastCellNum;

            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(i + 1);
                if (row != null)
                {
                    DataRow datarow = dt.NewRow();
                    for (int j = row.FirstCellNum; j < row.LastCellNum && j < herdrowNum; j++)
                    {
                        datarow[j] = row.GetCell(j) == null ? string.Empty : row.GetCell(j).ToString();
                    }

                    dt.Rows.Add(datarow);
                }
            }
            return dt;

}

#endregion

4、導出excel時漢化列明。

          DataTable dtExport = new DataTable();

            //漢化的表頭
            dtExport.Columns.Add(Resources.ExportColumns.GUID/*ID*/, typeof(string));
            dtExport.Columns.Add(Resources.ExportColumns.TASK_ID/*任務號*/, typeof(string));
            dtExport.Columns.Add(Resources.ExportColumns.CONTENT/*內容*/, typeof(string));
            dtExport.Columns.Add(Resources.ExportColumns.CREATOR_DATE/*日期*/, typeof(string));
            dtExport.Columns.Add(Resources.ExportColumns.CREATOR_NAME/*創建人*/, typeof(string));
            dtExport.Columns.Add(Resources.ExportColumns.CREATOR_ID/*創建工號*/, typeof(string));

            int colNo = 0;
            foreach (DataRow dr in dt.Rows)
            {
                colNo = 0;
                DataRow drExport = dtExport.NewRow();
                drExport[colNo++] = Convert.ToString(dr["GUID"]);
                drExport[colNo++] = Convert.ToString(dr["TASK_ID"]);
                drExport[colNo++] = Convert.ToString(dr["CONTENT"]);
                drExport[colNo++] = Convert.ToString(dr["CREATOR_DATE"]);
                drExport[colNo++] = Convert.ToString(dr["CREATOR_ID"]);
                drExport[colNo++] = Convert.ToString(dr["CREATOR_NAME"]);
                dtExport.Rows.Add(drExport);
            }
            dtExport.AcceptChanges();