NPOI操作EXCEL的简单例子

来源：互联网发布：wireshark ubuntu下载编辑：程序博客网时间：2024/05/24 03:23

1.将DataTable生成XLS文件

用一个单独类来写的哦。

        public static void Import(DataTable dt, string filename)        {            //创建空白文档            HSSFWorkbook workbook = new HSSFWorkbook();            //创建标准三页            HSSFSheet sheet1 = workbook.CreateSheet("sheet1");            HSSFSheet sheet2 = workbook.CreateSheet("sheet2");            HSSFSheet sheet3 = workbook.CreateSheet("sheet3");            HSSFRow headrow = sheet1.CreateRow(0);//创建第一行            //将DataTable的列写入第一行            foreach (DataColumn column in dt.Columns)            {                //column.Oradinal,列的位置,column.ColumnName,列名                headrow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);//写入                          }            //再写入记录值             HSSFRow row;            for (int i = 0; i < dt.Rows.Count;i++ )            {                row = sheet1.CreateRow(i + 1);                for (int j = 0; j < dt.Columns.Count;j++ )                {                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());//dataTable和EXCEL差不多都是一个二维数组一样的.                }            }            using(FileStream file=new FileStream(@"d:\"+filename+"",FileMode.Create))            {                workbook.Write(file);//写入                file.Dispose();                            }                }

调用的时候，简单的

 DataTable dt=SqlHelper.ExecuteAdapter("select id as '编号',name as '姓名',bus as '交通工具',tel as '电话号码' from Employee");           NPOIDataTable.Import(dt,"16.xls");

2.EXCEL写入DataTable中

  public static DataTable ImportData(string filename)        {            DataTable dt=new DataTable();            HSSFWorkbook workbook;            using(FileStream file=new FileStream(filename,FileMode.Open,FileAccess.Read))            {              workbook=new HSSFWorkbook(file);//读入Steam流            }            HSSFSheet sheet = workbook.GetSheetAt(0);//获取第一页            HSSFRow headrow = sheet.GetRow(0);//得到第一行            HSSFCell cell;            for (int i = 0; i < headrow.LastCellNum; i++)            {                cell = headrow.GetCell(i);                dt.Columns.Add(cell.ToString());                        }            for (int i = 0; i < sheet.LastRowNum; i++)            {                HSSFRow row = sheet.GetRow(i + 1);                DataRow datarow = dt.NewRow();//新建一行                for (int j = row.FirstCellNum; j < row.LastCellNum;j++ )                {                    datarow[j] = row.GetCell(j).ToString();//一行中，各个列的值                }                dt.Rows.Add(datarow);            }                return dt;        }