/// <summary> /// 设置单元格内容 /// </summary> public static void SetCellTitleStyle(string sheetName, string colName, int row, int col, int rowHeight, string value,bool isCreate) { ISheet sheet = workbook.GetSheet(sheetName); IRow dataRow; if (isCreate) { dataRow = sheet.CreateRow(row); } else { dataRow = sheet.GetRow(row); } ICell colCell = dataRow.CreateCell(col); //应用单元格样式 ICellStyle titleStyle = NPOIHelper.SetCellStyle(workbook, BorderStyle.Thin, HSSFColor.Black.Index, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.Orange.Index); IFont titleFont = NPOIHelper.SetFont(workbook, "宋体", 0, 700, 10); titleStyle.SetFont(titleFont); colCell.CellStyle = titleStyle; //设置单元格标题 colCell.SetCellValue(colName); if (!ValidateHelper.IsNullOrEmpty(value)) { ICell valCell = dataRow.CreateCell(col + 1); //应用单元格样式 valCell.CellStyle = GetCellStyle();; //设置单元格标题 valCell.SetCellValue(value); } dataRow.HeightInPoints = rowHeight; }
/// <summary> /// 导出excel /// </summary> /// <Author>刘小贵</Author> /// <CreateDate>2017.08.18</CreateDate> public void DownloadExcel() { System.IO.MemoryStream ms = NPOIHelper.ExportDT(_data, this._titleName); _response.Clear(); _response.AddHeader("content-disposition", "attachment; filename=" + this._fileName); _response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; _response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); _response.Flush(); _response.End(); }
//public static object JsonToObject(string jsonString, object obj) //{ // IList<Column> list = ConverHelper.JsonToListObj<Column>(jsonString); // DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType()); // MemoryStream mStream = new MemoryStream(Encoding.UTF8.GetBytes(jsonString)); // return serializer.ReadObject(mStream); //} public void Download() { List <Column> list_tmp = new List <Column>(); int n = -1; //判断EasyUI DataGrid 需要导出的列 for (int i = 0; i < _colName.Count; i++) { n++; var item = _colName[i]; if (item.hidden || _colName[i].title == null) { n--; continue; } list_tmp.Add(item); } List <string> pos = new List <string>(); //更改DataTable列名 for (int i = 0; i < list_tmp.Count; i++) { for (int j = 0; j < _data.Columns.Count; j++) { if (list_tmp[i].field == _data.Columns[j].ColumnName) {//有field和DataTable名字相同的则修改列名,使之和DataGrid的Title一样 _data.Columns[j].ColumnName = list_tmp[i].title; pos.Add(_data.Columns[j].ColumnName); break; } } } List <string> noPos = new List <string>(); //查找 无用的列 for (int i = 0; i < _data.Columns.Count; i++) { bool flag = false; for (int j = 0; j < pos.Count; j++) { if (pos[j] == _data.Columns[i].ColumnName) { flag = true; break; } } if (flag == false) { noPos.Add(_data.Columns[i].ColumnName); } } //删除 无用的列 for (int i = 0; i < noPos.Count; i++) { _data.Columns.Remove(noPos[i]); } System.IO.MemoryStream ms = NPOIHelper.ExportDT(_data, this._titleName); _response.Clear(); _response.AddHeader("content-disposition", "attachment; filename=" + this._fileName); _response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; _response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); _response.Flush(); _response.End(); }
/// <summary> /// 写入表数据 /// </summary> public static int SetCellListValue(DataTable dt, string sheetName, int row) { int rowIndex = row; int i = 1; ISheet sheet = workbook.GetSheet(sheetName); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow r in dt.Rows) { var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); var drValue = r[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (IsNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } 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; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } if(dt.Rows.Count - i == 0) { ICellStyle sStyle = NPOIHelper.SetCellStyle(workbook, BorderStyle.Thin, HSSFColor.Black.Index, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.SkyBlue.Index); newCell.CellStyle = GetModificationCellStyle(sStyle); } else { newCell.CellStyle = GetCellStyle(); } //单元格宽度自适应属性 int colWidth = sheet.GetColumnWidth(column.Ordinal) / 256; //获取当前列宽度 int length = Encoding.UTF8.GetBytes(r[column].ToString()).Length; //获取当前单元格的内容宽度 //若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符 if (colWidth < length + 1) { colWidth = length + 1; } sheet.SetColumnWidth(column.Ordinal, colWidth * 256); } //行高自适应 ICell currCell = dataRow.GetCell(1); int len = Encoding.UTF8.GetBytes(currCell.ToString()).Length; dataRow.HeightInPoints = 20 * (len / 60 + 1); rowIndex++; i++; } return rowIndex; }