public void Create(string sheetName, string[][] content, string fullPath)
{
try
{
book = new HSSFWorkbook();
sheet = book.CreateSheet(sheetName) as HSSFSheet;
for (int i = 0; i < content.Count(); i++)
{
if (content[i].Any())
{
AddTitle(sheet, i, content[i][0]);
}
if (content[i].Count() > 1)
{
AddColumnContent(sheet, i, content[i].Skip(1).ToList <string>());
}
}
file = new FileStream(fullPath, FileMode.OpenOrCreate);
book.Write(file);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
if (file != null)
{
file.Flush();
file.Close();
}
if (sheet != null)
{
sheet.Dispose();
}
if (book != null)
{
book.Dispose();
}
}
}
/// <summary>
/// NPOI简单Demo，快速入门代码
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
//填充表头
HSSFRow dataRow = sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
sheet.Dispose();
workbook.Dispose();
}
//private static WriteLog wl = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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 == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
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;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[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;
}
else
{
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;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
private static MemoryStream Export(DataGridView dgvSource)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = new int[dgvSource.Columns.Count];
foreach (DataGridViewColumn item in dgvSource.Columns)
{
if (item.Visible)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
}
}
for (int i = 0; i < dgvSource.Rows.Count; i++)
{
for (int j = 0; j < dgvSource.Columns.Count; j++)
{
if (dgvSource.Columns[j].Visible)
{
if (dgvSource.Rows[i].Cells[j].Value == null)
{
dgvSource.Rows[i].Cells[j].Value = "";
}
int intTemp = Encoding.GetEncoding(936).GetBytes(dgvSource.Rows[i].Cells[j].Value.ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
int rowIndex = 0;
int columnIndex = 0;
foreach (DataGridViewRow row in dgvSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
#region 列头及样式
{
HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataGridViewColumn column in dgvSource.Columns)
{
if (column.Visible)
{
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[column.Index] + 1) * 256);
columnIndex++;
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
columnIndex = 0;
#region 填充内容
HSSFRow dataRow = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataGridViewColumn column in dgvSource.Columns)
{
if (column.Visible)
{
HSSFCell newCell = (NPOI.HSSF.UserModel.HSSFCell)dataRow.CreateCell(columnIndex);
string drValue = row.Cells[column.Index].Value.ToString();
if (column.ValueType == null)
{
column.ValueType = Type.GetType("System.String");
}
switch (column.ValueType.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;
}
columnIndex++;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = this.Author;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
HSSFDataFormat format = workbook.CreateDataFormat();
//取得列宽
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 == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(0);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont 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;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell 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(string.Format("{0:yyyy-MM-dd}", dateV));
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;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
/// <summary>
/// ListToMemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="nameList">列头信息</param>
public MemoryStream Export <T>(BindingList <T> list, string strHeaderText, List <ExcelHeader> hearderList)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet(strHeaderText);
#region 文件属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = this.Author;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = Author;
si.ApplicationName = ApplicationName;
si.Comments = Comments;
si.Title = strHeaderText;
si.Subject = strHeaderText;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(0);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 600;
headStyle.SetFont(font);
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;
headStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index;
headStyle.BorderBottom = CellBorderType.THIN;
headStyle.BorderLeft = CellBorderType.THIN;
headStyle.BorderRight = CellBorderType.THIN;
headStyle.BorderTop = CellBorderType.THIN;
for (int i = 0; i < hearderList.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(hearderList[i].HeaderText);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.SetColumnWidth(i, hearderList[i].Width * 40);//设置列宽
}
headerRow.Dispose();
}
#endregion
int rowIndex = 1;
HSSFRow dataRow;
HSSFCellStyle style = workbook.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BorderTop = CellBorderType.THIN;
foreach (Object obj in list)
{
dataRow = sheet.CreateRow(rowIndex);
PropertyInfo[] pis = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//就是这个对象所有属性的集合
if (pis != null)
{
for (int j = 0; j < hearderList.Count; j++)
{
HSSFCell newCell = dataRow.CreateCell(j);
ExcelHeader eh = hearderList[j];
newCell.CellStyle = style;
foreach (PropertyInfo pi in pis)//针对每一个属性进行循环
{
if (eh.PropertyName.Equals(pi.Name))
{
object PropertyValue = pi.GetValue(obj, null);
if (PropertyValue != null)
{
string drValue = PropertyValue.ToString();
switch (PropertyValue.GetType().ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
if (eh.StringFormat != null)
{
newCell.SetCellValue(string.Format(eh.StringFormat, dateV));
}
else
{
newCell.SetCellValue(string.Format("{0:yyyy-MM-dd}", dateV));
}
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;
}
}
}
}
}
}
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion 通用导出
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
HSSFDataFormat 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 == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont 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;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell 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;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <returns></returns>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
HSSFDataFormat 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;
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
headStyle.BorderBottom = CellBorderType.THIN;
headStyle.BorderLeft = CellBorderType.THIN;
headStyle.BorderRight = CellBorderType.THIN;
headStyle.BorderTop = CellBorderType.THIN;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (strHeaderText != "")
{
HSSFRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyles = workbook.CreateCellStyle();
headStyles.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = 700;
headStyles.SetFont(font);
headStyles.Alignment = CellHorizontalAlignment.CENTER;
headStyles.BorderBottom = CellBorderType.THIN;
headStyles.BorderLeft = CellBorderType.THIN;
headStyles.BorderRight = CellBorderType.THIN;
headStyles.BorderTop = CellBorderType.THIN;
headerRow.GetCell(0).CellStyle = headStyles;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
headerRow.Dispose();
rowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow.Dispose();
}
#endregion
rowIndex++;
}
#endregion
#region 填充内容
foreach (DataColumn column in dtSource.Columns)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);
HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = headStyle;
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;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
// Token: 0x06000042 RID: 66 RVA: 0x000064B8 File Offset: 0x000046B8
protected override void View()
{
this.examinfo = DbHelper.ExecuteModel <ExamInfo>(this.examid);
if (this.examinfo.id == 0)
{
this.ShowErr("对不起，该试卷不存在或已被删除。");
}
else
{
this.sortid = this.examinfo.sortid;
this.sortinfo = SortBll.GetSortInfo(this.sortid);
if (this.ispost)
{
if (this.action == "delete")
{
string @string = FPRequest.GetString("chkid");
if (DbHelper.ExecuteDelete <ExamResult>(@string) > 0)
{
SqlParam sqlParam = DbHelper.MakeAndWhere("resultid", WhereType.In, @string);
DbHelper.ExecuteDelete <ExamResultTopic>(new SqlParam[]
{
sqlParam
});
}
}
}
if (this.examinfo.examdeparts == "" && this.examinfo.examuser == "" && this.examinfo.examroles == "")
{
List <SqlParam> list = new List <SqlParam>();
list.Add(DbHelper.MakeAndWhere("examid", this.examid));
if (this.keyword != "")
{
string text = "0";
SqlParam sqlParam2 = DbHelper.MakeAndWhere(string.Format("([username] LIKE '%{0}%' OR [realname] LIKE '%{0}%')", this.keyword), WhereType.Custom, "");
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
list.Add(DbHelper.MakeAndWhere("uid", WhereType.In, text));
}
if (this.action == "export")
{
OrderByParam[] orderbys = new OrderByParam[]
{
DbHelper.MakeOrderBy("score", OrderBy.DESC),
DbHelper.MakeOrderBy("id", OrderBy.ASC)
};
this.examresultlist = DbHelper.ExecuteList <ExamResult>(orderbys, list.ToArray());
}
else
{
this.examresultlist = DbHelper.ExecuteList <ExamResult>(this.pager, list.ToArray());
}
}
else
{
string text = "";
if (this.examinfo.examroles != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("roleid", WhereType.In, this.examinfo.examroles);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
if (this.examinfo.examdeparts != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("departid", WhereType.In, this.examinfo.examdeparts);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
if (this.examinfo.examuser != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("id", WhereType.In, this.examinfo.examuser);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
SqlParam sqlParam3 = DbHelper.MakeAndWhere("examid", this.examid);
OrderByParam orderby = DbHelper.MakeOrderBy("id", OrderBy.ASC);
List <ExamResult> list3 = DbHelper.ExecuteList <ExamResult>(orderby, new SqlParam[]
{
sqlParam3
});
int num = 0;
foreach (ExamResult examResult2 in this.examresultlist)
{
foreach (ExamResult examResult3 in list3)
{
if (examResult3.uid == examResult2.uid)
{
this.examresultlist[num].id = examResult3.id;
this.examresultlist[num].score = examResult3.score;
this.examresultlist[num].starttime = examResult3.starttime;
this.examresultlist[num].examdatetime = examResult3.examdatetime;
this.examresultlist[num].utime = examResult3.utime;
this.examresultlist[num].status = examResult3.status;
this.examresultlist[num].questions++;
this.examresultlist[num].ip = examResult3.ip;
}
}
num++;
}
if (this.keyword != "")
{
list3 = new List <ExamResult>();
foreach (ExamResult examResult2 in this.examresultlist)
{
if (examResult2.IUser.username.Contains(this.keyword) || examResult2.IUser.realname.Contains(this.keyword))
{
list3.Add(examResult2);
}
}
this.examresultlist = new List <ExamResult>();
foreach (ExamResult examResult2 in list3)
{
this.examresultlist.Add(examResult2);
}
}
if (this.action != "export" && this.action != "report")
{
this.pager.total = this.examresultlist.Count;
int num2 = (this.pager.pageindex - 1) * this.pager.pagesize;
int count = this.pager.pagesize;
if (num2 + this.pager.pagesize > this.pager.total)
{
count = this.pager.total - num2;
}
this.examresultlist = this.examresultlist.GetRange(num2, count);
}
}
if (this.ispost)
{
if (this.action == "export")
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet hssfsheet = hssfworkbook.CreateSheet("Sheet1");
HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle();
hssfcellStyle.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle.BorderTop = CellBorderType.THIN;
hssfcellStyle.BorderRight = CellBorderType.THIN;
hssfcellStyle.BorderLeft = CellBorderType.THIN;
hssfcellStyle.BorderBottom = CellBorderType.THIN;
hssfcellStyle.DataFormat = 0;
HSSFFont hssffont = hssfworkbook.CreateFont();
hssffont.Boldweight = short.MaxValue;
hssfcellStyle.SetFont(hssffont);
HSSFRow hssfrow = hssfsheet.CreateRow(0);
hssfrow.CreateCell(0).SetCellValue("用户名");
hssfrow.CreateCell(1).SetCellValue("姓名");
hssfrow.CreateCell(2).SetCellValue("所在部门");
hssfrow.CreateCell(3).SetCellValue("考试得分");
hssfrow.CreateCell(4).SetCellValue("开始时间");
hssfrow.CreateCell(5).SetCellValue("考试用时");
hssfrow.CreateCell(6).SetCellValue("考试状态");
hssfrow.CreateCell(7).SetCellValue("");
hssfrow.Height = 400;
hssfsheet.SetColumnWidth(2, 6000);
hssfsheet.SetColumnWidth(4, 6000);
for (int i = 0; i < 7; i++)
{
hssfrow.Cells[i].CellStyle = hssfcellStyle;
}
HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle();
hssfcellStyle2.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle2.BorderTop = CellBorderType.THIN;
hssfcellStyle2.BorderRight = CellBorderType.THIN;
hssfcellStyle2.BorderLeft = CellBorderType.THIN;
hssfcellStyle2.BorderBottom = CellBorderType.THIN;
hssfcellStyle2.DataFormat = 0;
int num3 = 1;
foreach (ExamResult examResult2 in this.examresultlist)
{
HSSFRow hssfrow2 = hssfsheet.CreateRow(num3);
hssfrow2.Height = 300;
hssfrow2.CreateCell(0).SetCellValue(examResult2.IUser.username);
hssfrow2.CreateCell(1).SetCellValue(examResult2.IUser.realname);
hssfrow2.CreateCell(2).SetCellValue(examResult2.IUser.Department.name);
hssfrow2.CreateCell(3).SetCellValue(examResult2.score.ToString());
if (examResult2.status >= 0)
{
hssfrow2.CreateCell(4).SetCellValue(examResult2.examdatetime.ToString("yyyy-MM-dd HH:mm:dd"));
hssfrow2.CreateCell(5).SetCellValue((examResult2.utime / 60 + 1).ToString() + "分钟");
}
else
{
hssfrow2.CreateCell(4).SetCellValue("");
hssfrow2.CreateCell(5).SetCellValue("");
}
if (examResult2.status == 1)
{
hssfrow2.CreateCell(6).SetCellValue("已交卷");
}
else if (examResult2.status == 2)
{
hssfrow2.CreateCell(6).SetCellValue("已阅卷");
}
else if (examResult2.status == 0)
{
hssfrow2.CreateCell(6).SetCellValue("未交卷");
}
else
{
hssfrow2.CreateCell(6).SetCellValue("缺考");
}
hssfrow2.CreateCell(7).SetCellValue("");
for (int i = 0; i < 7; i++)
{
hssfrow2.Cells[i].CellStyle = hssfcellStyle2;
}
num3++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
hssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0L;
hssfsheet.Dispose();
hssfworkbook.Dispose();
base.Response.ContentType = "application/vnd.ms-excel";
base.Response.ContentEncoding = Encoding.UTF8;
base.Response.Charset = "";
base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.examinfo.name + "成绩表.xls"));
base.Response.BinaryWrite(memoryStream.GetBuffer());
base.Response.Flush();
base.Response.End();
}
}
else if (this.action == "report")
{
AsposeWordApp asposeWordApp = new AsposeWordApp();
asposeWordApp.Open(FPUtils.GetMapPath("images\\examreport.doc"));
asposeWordApp.InsertText("examtitle", this.examinfo.name);
asposeWordApp.InsertText("username", this.user.realname);
asposeWordApp.InsertText("total", this.examinfo.total.ToString() + "分");
if (this.examinfo.islimit == 1)
{
asposeWordApp.InsertText("examtime", this.examinfo.starttime.ToString("yyyy-MM-dd HH:mm"));
}
else
{
asposeWordApp.InsertText("examtime", "不限制");
}
asposeWordApp.InsertText("exampass", (this.examinfo.passmark * this.examinfo.total / 100.0).ToString() + "分");
asposeWordApp.InsertText("qtime", this.examinfo.examtime.ToString() + "分钟");
asposeWordApp.InsertText("examuser", this.examinfo.exams.ToString() + "人");
if (this.examinfo.exams > 0)
{
asposeWordApp.InsertText("examavg", (this.examinfo.score / (double)this.examinfo.exams).ToString("0.0"));
}
else
{
asposeWordApp.InsertText("examavg", "0");
}
int[] array = new int[5];
foreach (ExamResult examResult2 in this.examresultlist)
{
if (examResult2.score < 60.0)
{
array[0]++;
}
else if (examResult2.score >= 60.0 && examResult2.score < 70.0)
{
array[1]++;
}
else if (examResult2.score >= 70.0 && examResult2.score < 80.0)
{
array[2]++;
}
else if (examResult2.score >= 80.0 && examResult2.score < 90.0)
{
array[3]++;
}
else if (examResult2.score >= 90.0)
{
array[4]++;
}
}
int i = 1;
foreach (int num4 in array)
{
asposeWordApp.InsertText("s" + i, num4.ToString() + "人");
asposeWordApp.InsertText("p" + i, (num4 / this.examinfo.exams * 100).ToString("0.0") + "%");
i++;
}
asposeWordApp.Save(base.Response, this.examinfo.name + "_考试分析报告.doc");
}
}
base.SaveRightURL();
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText = "")
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 全局属性
//int maxRowIndex = 65535;
int lastRowNum = 0;
int colMaxWidth = 255;//超过255会报错
//表格统一样式
HSSFCellStyle allCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
allCellStyle.BorderTop = CellBorderType.THIN;
allCellStyle.BorderBottom = CellBorderType.THIN;
allCellStyle.BorderLeft = CellBorderType.THIN;
allCellStyle.BorderRight = CellBorderType.THIN;
//时间样式
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
dateStyle.CloneStyleFrom(allCellStyle);
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//表头、列头样式
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.CloneStyleFrom(allCellStyle);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 15;
headStyle.SetFont(font);
// 列头样式
HSSFCellStyle columnHeadStyle = (HSSFCellStyle)workbook.CreateCellStyle();
columnHeadStyle.CloneStyleFrom(allCellStyle);
columnHeadStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font2 = (HSSFFont)workbook.CreateFont();
font2.Boldweight = 700;
columnHeadStyle.SetFont(font2);
#endregion
#region 新建表，设置表头、整体样式
//表头
if (strHeaderText != "")
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
headerRow.HeightInPoints = 20;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = headStyle;
headerRow.GetCell(0).SetCellValue(strHeaderText);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
//取得列宽
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 > colMaxWidth ? colMaxWidth : intTemp;
}
}
}
//设置列宽、填充列头
HSSFRow columRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
foreach (DataColumn column in dtSource.Columns)
{
//songliangliang 2014.7.1
//原 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
HSSFCell newCell = (HSSFCell)columRow.CreateCell(column.Ordinal);
newCell.CellStyle = columnHeadStyle;
ConfigCell(ref newCell, column.ColumnName);
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]) * 256);
}
#endregion
#region 填充表格内容
//int rowIndex = sheet.LastRowNum;
foreach (DataRow row in dtSource.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
ConfigCell(ref newCell, row[column], dateStyle);
newCell.CellStyle = allCellStyle;
}
//rowIndex++;
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 根据Excel获取集合信息
/// </summary>
/// <param name="fileName">Excel存储路径</param>
/// <returns></returns>
public List <IMovieShowList.MovieShow> GetList4Excel(string fileName)
{
List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();
HSSFWorkbook wk = null;
using (FileStream fs = System.IO.File.Open(fileName, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
wk = new HSSFWorkbook(fs);
fs.Close();
}
HSSFSheet sheet = (HSSFSheet)wk.GetSheetAt(0);
if (sheet != null)
{
int rowIndex = 0;
NPOI.SS.UserModel.Row row = null;
while ((row = sheet.GetRow(rowIndex)) != null)
{
try
{
Cell c1 = row.GetCell(0);
string room = c1.RichStringCellValue.String;
Cell c2 = row.GetCell(1);
string tim = c2.RichStringCellValue.String;
Cell c3 = row.GetCell(2);
string name = c3.RichStringCellValue.String;
if (string.IsNullOrWhiteSpace(room) || string.IsNullOrWhiteSpace(tim) || string.IsNullOrWhiteSpace(name))
{
rowIndex++;
continue;
}
string time = ParseBeginTime(tim);
list.Add(new IMovieShowList.MovieShow()
{
Room = room,
BeginTime = time,
Name = name
});
rowIndex++;
}
catch (Exception ex)
{
isOk = false;
Msg = "xcel文件中的时间有错误,在" + (rowIndex + 1) + "行" + Environment.NewLine + ex.Message;
break;
}
}
sheet.Dispose();
}
return(list);
}
// Token: 0x06000076 RID: 118 RVA: 0x0000BE54 File Offset: 0x0000A054
protected override void View()
{
this.examconfig = ExamConifgs.GetExamConfig();
this.sortinfo = SortBll.GetSortInfo(this.sortid);
if (this.sortinfo.id <= 0)
{
this.ShowErr("该题库已被删除或不存在");
}
else
{
if (this.channelid == 0)
{
this.channelid = this.sortinfo.channelid;
}
string childSorts = SortBll.GetChildSorts(this.sortinfo);
List <SqlParam> list = new List <SqlParam>();
list.Add(DbHelper.MakeAndWhere("sortid", WhereType.In, childSorts));
if (this.type > 0)
{
list.Add(DbHelper.MakeAndWhere("type", this.type));
}
if (this.keyword != "")
{
list.Add(DbHelper.MakeAndWhere("title", WhereType.Like, this.keyword));
}
if (this.ispost)
{
if (this.action == "delete")
{
string @string = FPRequest.GetString("chkid");
string questionSorts = QuestionBll.GetQuestionSorts(@string);
SortBll.UpdateSortPosts(questionSorts, -1);
DbHelper.ExecuteDelete <ExamQuestion>(@string);
}
else if (this.action == "clear")
{
DbHelper.ExecuteDelete <ExamQuestion>(new SqlParam[]
{
list[0]
});
}
else if (this.action == "move")
{
string @string = FPRequest.GetString("chkid");
if (@string == "")
{
this.ShowErr("对不起，您未选择任何选项");
return;
}
base.Response.Redirect(string.Concat(new object[]
{
"questionmove.aspx?channelid=",
this.channelid,
"&sortid=",
this.sortid,
"&pageindex=",
this.pager.pageindex,
"&chkid=",
@string
}));
}
else if (this.action == "export")
{
List <ExamQuestion> list2 = DbHelper.ExecuteList <ExamQuestion>(list.ToArray());
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet hssfsheet = hssfworkbook.CreateSheet("Sheet1");
HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle();
hssfcellStyle.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle.BorderTop = CellBorderType.THIN;
hssfcellStyle.BorderRight = CellBorderType.THIN;
hssfcellStyle.BorderLeft = CellBorderType.THIN;
hssfcellStyle.BorderBottom = CellBorderType.THIN;
hssfcellStyle.DataFormat = 0;
HSSFFont hssffont = hssfworkbook.CreateFont();
hssffont.Boldweight = short.MaxValue;
hssfcellStyle.SetFont(hssffont);
HSSFRow hssfrow = hssfsheet.CreateRow(0);
hssfrow.CreateCell(0).SetCellValue("题目类型");
hssfrow.CreateCell(1).SetCellValue("题目标题");
hssfrow.CreateCell(2).SetCellValue("选项A");
hssfrow.CreateCell(3).SetCellValue("选项B");
hssfrow.CreateCell(4).SetCellValue("选项C");
hssfrow.CreateCell(5).SetCellValue("选项D");
hssfrow.CreateCell(6).SetCellValue("选项E");
hssfrow.CreateCell(7).SetCellValue("选项F");
hssfrow.CreateCell(8).SetCellValue("正确答案");
hssfrow.CreateCell(9).SetCellValue("答案关键词");
hssfrow.CreateCell(10).SetCellValue("答案解释");
hssfrow.CreateCell(11).SetCellValue("难易程度");
hssfrow.CreateCell(12).SetCellValue("随机题目");
hssfrow.CreateCell(13).SetCellValue("所在题库");
hssfrow.CreateCell(14).SetCellValue("");
hssfrow.Height = 400;
hssfsheet.SetColumnWidth(1, 6000);
for (int i = 0; i < 14; i++)
{
hssfrow.Cells[i].CellStyle = hssfcellStyle;
}
HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle();
hssfcellStyle2.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle2.BorderTop = CellBorderType.THIN;
hssfcellStyle2.BorderRight = CellBorderType.THIN;
hssfcellStyle2.BorderLeft = CellBorderType.THIN;
hssfcellStyle2.BorderBottom = CellBorderType.THIN;
hssfcellStyle2.DataFormat = 0;
int num = 1;
foreach (ExamQuestion examQuestion in list2)
{
HSSFRow hssfrow2 = hssfsheet.CreateRow(num);
hssfrow2.Height = 300;
hssfrow2.CreateCell(0).SetCellValue(this.TypeStr(examQuestion.type));
hssfrow2.CreateCell(1).SetCellValue(examQuestion.title.Trim());
if (examQuestion.type == 1 || examQuestion.type == 2)
{
string[] array = FPUtils.SplitString(examQuestion.content, "§", 6);
int num2 = 0;
foreach (string cellValue in array)
{
hssfrow2.CreateCell(2 + num2).SetCellValue(cellValue);
num2++;
}
}
else if (examQuestion.type == 4)
{
if (examQuestion.upperflg == 1)
{
hssfrow2.CreateCell(2).SetCellValue("区分大小写");
}
else
{
hssfrow2.CreateCell(2).SetCellValue("");
}
if (examQuestion.orderflg == 1)
{
hssfrow2.CreateCell(3).SetCellValue("区分顺序");
}
else
{
hssfrow2.CreateCell(3).SetCellValue("");
}
hssfrow2.CreateCell(4).SetCellValue("");
hssfrow2.CreateCell(5).SetCellValue("");
hssfrow2.CreateCell(6).SetCellValue("");
hssfrow2.CreateCell(7).SetCellValue("");
}
else
{
hssfrow2.CreateCell(2).SetCellValue(examQuestion.content.Trim());
hssfrow2.CreateCell(3).SetCellValue("");
hssfrow2.CreateCell(4).SetCellValue("");
hssfrow2.CreateCell(5).SetCellValue("");
hssfrow2.CreateCell(6).SetCellValue("");
hssfrow2.CreateCell(7).SetCellValue("");
}
hssfrow2.CreateCell(8).SetCellValue(examQuestion.answer.Trim());
hssfrow2.CreateCell(9).SetCellValue(examQuestion.answerkey.Trim());
hssfrow2.CreateCell(10).SetCellValue(examQuestion.explain.Trim());
hssfrow2.CreateCell(11).SetCellValue(this.DifficultyStr(examQuestion.difficulty));
hssfrow2.CreateCell(12).SetCellValue((examQuestion.status == 1) ? "是" : "否");
hssfrow2.CreateCell(13).SetCellValue("");
hssfrow2.CreateCell(14).SetCellValue("");
for (int i = 0; i < 14; i++)
{
hssfrow2.Cells[i].CellStyle = hssfcellStyle2;
}
num++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
hssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0L;
hssfsheet.Dispose();
hssfworkbook.Dispose();
base.Response.ContentType = "application/vnd.ms-excel";
base.Response.ContentEncoding = Encoding.UTF8;
base.Response.Charset = "";
base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.sortinfo.name + "题库.xls"));
base.Response.BinaryWrite(memoryStream.GetBuffer());
base.Response.Flush();
base.Response.End();
}
}
}
this.questionlist = DbHelper.ExecuteList <ExamQuestion>(this.pager, list.ToArray());
if (this.sortinfo.posts != this.pager.total)
{
string sqlstring = string.Format("UPDATE [{0}WMS_SortInfo] SET [posts]={1} WHERE [id]={2}", DbConfigs.Prefix, this.pager.total, this.sortid);
DbHelper.ExecuteSql(sqlstring);
}
base.SaveRightURL();
}
}