/// <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;
}
