/// <summary> /// 将excel内容读到DataTable中 /// </summary> /// <param name="dt">存放数据DataTable</param> /// <param name="fileName">excel文件</param> /// <param name="iSheet">第几sheet页(0开头)</param> /// <param name="isFirstRowColumn">excel第一行为标题</param> /// <returns>数据行数</returns> public static int ReadExcel(ref DataTable dt, string fileName, int iSheet, bool isFirstRowColumn) { IWorkbook workbook = null; FileStream fs = null; ISheet sheet = null; int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } //if (sheetName != null) //{ // sheet = workbook.GetSheet(sheetName); // if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet // { // sheet = workbook.GetSheetAt(0); // } //} sheet = workbook.GetSheetAt(iSheet); if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); dt.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { dataRow[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dataRow); } } return(dt.Rows.Count); } catch (Exception ex) { throw new Exception("读取excel失败:" + ex.Message); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> private static MemoryStream Export(DataTable dtSource) { XSSFWorkbook workbook = new XSSFWorkbook(); //HSSFWorkbook workbook = new HSSFWorkbook(); //HSSFSheet sheet = workbook.CreateSheet(); ISheet sheet = workbook.CreateSheet(); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 列头及样式 { IRow headerRow = sheet.CreateRow(0); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 //解决导出excel表中的某个单元格数据过大,显示单元格最大列宽255错误 int colWidth = (arrColWidth[column.Ordinal] + 1) * 256; if (colWidth < 255 * 256) { sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth); } else { sheet.SetColumnWidth(column.Ordinal, 6000); } //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); //ms.Position = 0; workbook.Close(); //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// 从Excel获取值传递到对象的属性里 /// </summary> /// <param name="distanceType">目标对象类型</param> /// <param name="sourceCell">对象属性的值</param> private static Object GetExcelCellToProperty(Type distanceType, ICell sourceCell) { object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null; // 1.判断传递的单元格是否为空 if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString())) { return(rs); } // 2.Excel文本和数字单元格转换,在Excel里文本和数字是不能进行转换,所以这里预先存值 object sourceValue = null; switch (sourceCell.CellType) { case CellType.Blank: break; case CellType.Boolean: break; case CellType.Error: break; case CellType.Formula: break; case CellType.Numeric: sourceValue = sourceCell.NumericCellValue; break; case CellType.String: sourceValue = sourceCell.StringCellValue; break; case CellType.Unknown: break; default: break; } string valueDataType = distanceType.Name; // 在这里进行特定类型的处理 switch (valueDataType.ToLower()) // 以防出错,全部小写 { case "string": rs = sourceValue.ToString(); break; case "int": case "int16": case "int32": rs = (int)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType); break; case "float": case "single": rs = (float)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType); break; case "datetime": rs = sourceCell.DateCellValue; break; case "guid": rs = (Guid)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType); return(rs); } return(rs); }
//public void OutputBinayStream(System.IO.FileInfo file, HttpContext context) //{ // #region Output Binary Stream // context.Response.Clear(); // context.Response.Charset = "GB2312"; // context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name)); // context.Response.AddHeader("Content-Length", file.Length.ToString()); // context.Response.ContentType = "application/ms-excel"; // context.Response.WriteFile(file.FullName); // context.Response.End(); // #endregion //} #endregion #region 动态转换成excel public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap) { try { List <int> dateCellIndex = new List <int>();//日期格式列 bool header = true; //创建工作薄 HSSFWorkbook wk = new HSSFWorkbook(); //创建一个名称为mySheet的表 ISheet tb = wk.CreateSheet(SheetName); #region 表头样式 ICellStyle headStyle = wk.CreateCellStyle(); headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; headStyle.Alignment = HorizontalAlignment.Center; IFont font = wk.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 行样式 ICellStyle stylerow = wk.CreateCellStyle(); stylerow.Alignment = HorizontalAlignment.Center; IFont fontrow = wk.CreateFont(); fontrow.FontName = "宋体"; fontrow.FontHeightInPoints = 12; stylerow.SetFont(fontrow); #endregion #region 日期行样式 ICellStyle dateStyle = wk.CreateCellStyle(); IDataFormat format = wk.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); #endregion //合并标题行 /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count)); IRow rowhead = tb.CreateRow(0); //创建一行 rowhead.HeightInPoints = 25; //行高 ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cellhead.CellStyle = headStyle; cellhead.SetCellValue(HeadName); //写入表头 //SetCellRangeAddress(tb, 0, 0, 1, 20); int rowIndex = 1; if (header) { IRow row = tb.CreateRow(rowIndex); //创建一行 rowIndex++; row.HeightInPoints = 25; //行高 int cellIndex = 0; //开始列索引 foreach (var item in excelDataMap) { string columsName = item.Value; tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字 ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cell.CellStyle = headStyle; if (columsName == "DEPT_NAME") { cell.SetCellValue("单位名称"); } else { cell.SetCellValue(table.Columns[columsName].ToString());//循环往第二行的单元格中添加数据 } cellIndex++; } } for (int i = 0; i < table.Rows.Count; i++) { IRow row = tb.CreateRow(rowIndex + i); //创建一行 row.HeightInPoints = 20; //行高 int cellIndex = 0; //开始列索引 foreach (var item in excelDataMap) { string columsName = item.Value; ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cell.SetCellValue(table.Rows[i][columsName].ToString()); //循环往第二行的单元格中添加数据 cell.CellStyle = stylerow; cellIndex++; } } using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件! { wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 } return(true); } catch (Exception) { return(false); } }
public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className) { try { DataTable table = ToDataTable(listJob, className); List <int> dateCellIndex = new List <int>();//日期格式列 bool header = true; //创建工作薄 HSSFWorkbook wk = new HSSFWorkbook(); //创建一个名称为mySheet的表 ISheet tb = wk.CreateSheet(SheetName); #region 表头样式 ICellStyle headStyle = wk.CreateCellStyle(); headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; headStyle.Alignment = HorizontalAlignment.Center; IFont font = wk.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 行样式 ICellStyle stylerow = wk.CreateCellStyle(); stylerow.Alignment = HorizontalAlignment.Center; IFont fontrow = wk.CreateFont(); fontrow.FontName = "宋体"; fontrow.FontHeightInPoints = 12; stylerow.SetFont(fontrow); #endregion #region 日期行样式 ICellStyle dateStyle = wk.CreateCellStyle(); IDataFormat format = wk.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #endregion Dictionary <string, string> excelDataMap = ReadXml(className, false); //合并标题行 /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count)); IRow rowhead = tb.CreateRow(0); //创建一行 rowhead.HeightInPoints = 25; //行高 ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cellhead.CellStyle = headStyle; cellhead.SetCellValue(HeadName); //写入表头 //SetCellRangeAddress(tb, 0, 0, 1, 20); int rowIndex = 1; if (header) { IRow row = tb.CreateRow(rowIndex); //创建一行 rowIndex++; row.HeightInPoints = 25; //行高 int cellIndex = 0; //开始列索引 #region 弃用 //for (int i = 0; i < table.Columns.Count; i++) //{ // if (!table.Columns[i].ToString().EndsWith("_wennull")) // { // if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型 // { // tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字 // dateCellIndex.Add(cellIndex); // } // else // { // tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字 // } // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // cell.CellStyle = headStyle; // cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据 // cellIndex++; // } //} #endregion foreach (var item in excelDataMap) { string columsName = item.Value; if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型 { tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字 dateCellIndex.Add(cellIndex); } else { tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字 } ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cell.CellStyle = headStyle; cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据 cellIndex++; } } for (int i = 0; i < table.Rows.Count; i++) { IRow row = tb.CreateRow(rowIndex + i); //创建一行 row.HeightInPoints = 20; //行高 int cellIndex = 0; //开始列索引 #region 弃用 //for (int c = 0; c < table.Columns.Count; c++) //{ // if (!table.Columns[c].ToString().EndsWith("_wennull")) // { // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型 // { // string value = table.Rows[i][c].ToString(); // if (value != "") // { // cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据 // } // cell.CellStyle = dateStyle; // } // else // { // cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据 // cell.CellStyle = stylerow; // } // cellIndex++; // } //} #endregion foreach (var item in excelDataMap) { string columsName = item.Value; ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型 { string value = table.Rows[i][columsName].ToString(); if (value != "") { cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));//循环往第二行的单元格中添加数据 } cell.CellStyle = dateStyle; } else { cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据 cell.CellStyle = stylerow; } cellIndex++; } } //foreach (int cellIndex in dateCellIndex) //{ // for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式 // { // IRow row = tb.CreateRow(rowIndex + y);//创建一行 // row.HeightInPoints = 20; //行高 // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // //cell.SetCellValue("");//循环往第二行的单元格中添加数据 // cell.CellStyle = dateStyle; // } //} using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件! { wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 } return(true); } catch (Exception) { return(false); } }
/// <summary> /// excel读取返回DataTable,开始列必须是第一列 /// </summary> /// <param name="excelPath">excelPath路径</param> /// <param name="sheetIndex">要读取的工作表索引</param> /// <param name="rowIndex">开始行数</param> /// <param name="isHead">开始行是否是DataTable的列名</param> /// <returns></returns> public AjaxMsgModel ToDataTable(string excelPath, int sheetIndex, int rowIndex, AjaxMsgModel amm) { bool isHead = true;//第一行必须是表头 try { DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(excelPath) + "_Sheet" + sheetIndex); IWorkbook workbook; //workbook = new HSSFWorkbook(file);//创建对应文件 try { using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003 } } catch (Exception ex) { using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007 } } if (File.Exists(excelPath))//读完即删除 { File.Delete(excelPath); } ISheet sheet = workbook.GetSheetAt(sheetIndex); //按索引获取工作表 var rows = sheet.GetRowEnumerator(); //获取工作表中所有行HSSFSheet.GetRow(rowIndex)获取某一行 for (int i = 0; i < rowIndex; i++) //从第rowindex行开始读取 { rows.MoveNext(); } IRow row = (IRow)rows.Current; try { for (int i = 0; i < row.LastCellNum; i++)//给DataTable创建列 { ICell cell = row.GetCell(i); string columnName = isHead ? cell.ToString() : i.ToString(); if (columnName.Trim() == "") { amm.Statu = AjaxStatu.err; amm.Msg = "表头名不能为空!"; return(amm); } dt.Columns.Add(columnName.Trim(), typeof(string)); } } catch (Exception) { amm.Statu = AjaxStatu.err; amm.Msg = "表头名不能为空或有重复!"; return(amm); } if (!isHead)//如果第一行是列名则不加入DataTable { DataRow first = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); first[i] = cell.ToString(); } dt.Rows.Add(first); } while (rows.MoveNext()) { row = (IRow)rows.Current; DataRow dataRow = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell != null) { if (cell.CellType == CellType.Numeric)//如果是日期或数字 { string readvalue = GetCellValue(cell); if (readvalue.IndexOf("/") > -1)//如果是日期 { dataRow[i] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { dataRow[i] = readvalue; } } else { dataRow[i] = GetCellValue(cell); } //dataRow[i] = cell.ToString(); } else//如果是空白 { dataRow[i] = ""; } if (i == dt.Columns.Count - 1) { break;//如果行的列比表头多则不继续读取 } } dt.Rows.Add(dataRow); } amm.Data = removeEmpty(dt); amm.Statu = AjaxStatu.ok; amm.Msg = "读取文件内容成功!"; return(amm); } catch (Exception) { amm.Statu = AjaxStatu.err; amm.Msg = "读取文件内容失败!可能原因或为文件中没内容"; return(amm); } }