public void SaveFile()
{
using (FileStream stream = File.OpenWrite(this.Path))
{
_workbook.Write(stream);
}
_sheet.Dispose();
_workbook.Dispose();
}
/*
* 响应到客户端
*
* Param fileName 文件名
*/
public static void WriteToClient(String fileName, HSSFWorkbook workbook)
{
//Write the stream data of workbook to the root directory
//FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Expires = 0;
HttpContext.Current.Response.CacheControl = "no-cache";
HttpContext.Current.Response.ContentType = "application/x-excel";
//inline
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
workbook.Write(HttpContext.Current.Response.OutputStream);
Sheet sheet = workbook.GetSheetAt(0);
sheet = null;
workbook.Dispose();
workbook = null;
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
//file.Close();
}
/// <summary>
/// 将DataTable转成Xls文件导出
/// </summary>
/// <param name="page"></param>
/// <param name="dt"></param>
/// <param name="FileName"></param>
/// <returns></returns>
public static Stream ExportExcelByNPOI(DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//设定文件相关信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Data";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Data";
hssfworkbook.SummaryInformation = si;
//将DataTable数据写入
Sheet sheet1 = hssfworkbook.CreateSheet("DataExport_NPOI");
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
Row row = sheet1.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
hssfworkbook.Dispose();
return(file);
}
public static DataTable Import(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
Sheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
Row headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
ExcelFileStream.Close();
workbook.Dispose();
return(table);
}
/*
* 生成文件
*
* Param fileName 文件名
*
* Return 生成文件的URL
*/
public static string WriteToFile(String fileName, HSSFWorkbook workbook)
{
//临时文件路径
string tempFilePath = HttpContext.Current.Server.MapPath("~/" + BusinessConstants.TEMP_FILE_PATH);
string tempFileName = GetRandomFileName(fileName);
if (!Directory.Exists(tempFilePath))
{
Directory.CreateDirectory(tempFilePath);
}
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(tempFilePath + tempFileName, FileMode.Create);
workbook.Write(file);
file.Flush();
file.Close();
file.Dispose();
file = null;
Sheet sheet = workbook.GetSheetAt(0);
sheet = null;
workbook.Dispose();
workbook = null;
return(GetShowFileUrl(tempFileName));
}
/// <summary>
/// NPOI简单Demo，快速入门代码
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//填充表头
HSSFRow dataRow = (HSSFRow)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 = (HSSFRow)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(fs);
}
}
workbook.Dispose();
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
Workbook.Dispose();
disposed = true;
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextDic">头文本</param>
/// <param name="fileName">文件名</param>
/// <param name="isShowTitle">是否显示表头</param>
/// <returns></returns>
private static MemoryStream Export(DataTable dtSource, Dictionary <string, string> headerTextDic, string fileName, bool isShowTitle)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet() as HSSFSheet;
SetSummaryInformation(workbook);
var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
var format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var arrColWidth = GetColWidth(dtSource, headerTextDic);
var rowIndex = 0;
var colHeaderStyle = GetColHeaderStyle(workbook);
var contentDataStyle = GetContentDataStyle(workbook);
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 65536 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
SetDataTitle(headerTextDic, fileName, isShowTitle, workbook, sheet);
SetColHeader(dtSource, headerTextDic, isShowTitle, workbook, sheet, arrColWidth, colHeaderStyle);
if (isShowTitle)
{
rowIndex = 2;
}
else
{
rowIndex = 1;
}
}
var dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
SetContentData(dtSource, headerTextDic, dateStyle, row, dataRow, workbook, contentDataStyle);
rowIndex++;
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
public void TestNPOIBug6341()
{
using (HSSFWorkbook workbook = OpenSample("Simple.xls"))
{
int i = workbook.ActiveSheetIndex;
}
using (HSSFWorkbook workbook = OpenSample("blankworkbook.xls"))
{
int i = workbook.ActiveSheetIndex;
}
HSSFWorkbook workbook2 = OpenSample("blankworkbook.xls");
workbook2.Dispose();
NPOI.SS.UserModel.Sheet sheet = workbook2.GetSheetAt(1);
}
protected virtual void CreateExcel(DateTime?from, DateTime?to)
{
//总账科目汇总
List <SAPInterfaceExchangeInfo> lstAll = m_ReconReportDA.CreateExcel(from, to);
//总账科目差异
List <SAPInterfaceExchangeInfo> lstUnbalance = GetUnbalance(lstAll);
DateTime now = DateTime.Now;
string dateStr = to.Value.Date.ToShortDateString().Replace("/", "-");
string beginDateStr = from.Value.ToShortDateString().Replace("-", "/");
string endDateStr = to.Value.Date.ToShortDateString().Replace("-", "/");
string fileName = ReconReportConfig.BasicDirectory + "SAPInterface_" + dateStr + ".xls";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ReconReportExportData reporter = new ReconReportExportData();
reporter.begin = beginDateStr;
reporter.end = endDateStr;
Sheet sheet1 = hssfworkbook.CreateSheet("SAPInterface_" + dateStr + "(S)");
reporter.ImportDataToSheet(sheet1, lstAll);
//统驭科目(AR/AP)汇总
List <SAPInterfaceExchangeInfo> lstOther = m_ReconReportDA.CreateOtherExcel(from, to);
//统驭账科目差异
List <SAPInterfaceExchangeInfo> lstOtherUnbalance = GetUnbalance(lstOther);
ReconOtherReportExportData OtherReporter = new ReconOtherReportExportData();
OtherReporter.begin = beginDateStr;
OtherReporter.end = endDateStr;
Sheet sheet2 = hssfworkbook.CreateSheet("AR&AP Report_" + dateStr);
OtherReporter.ImportDataToSheet(sheet2, lstOther);
FileStream file = new FileStream(fileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
hssfworkbook.Dispose();
//上传
string ftpurl = UpLoad(fileName);
//发邮件
SendEmail(ftpurl, dateStr, lstUnbalance, lstOtherUnbalance);
}
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>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
//填充表头
Row 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();
}
}
workbook.Dispose();
}
public string WriteExcel(List <CustomerInfo> list)
{
string reportFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Reports");
if (!Directory.Exists(reportFolder))
{
Directory.CreateDirectory(reportFolder);
}
string reportFile = Path.Combine(reportFolder, string.Format("CustomerReportInfo-{0:yyyyMMddHHmmss}.xls", DateTime.Now));
List <List <CustomerInfo> > lists = new List <List <CustomerInfo> >();
for (int k = 0; k < list.Count; k++)
{
if (k % 50000 == 0)
{
lists.Add(new List <CustomerInfo>());
}
lists[lists.Count - 1].Add(list[k]);
}
for (int i = 0; i < lists.Count; i++)
{
Sheet sheet = hssWorkBook.CreateSheet("CustomerInfo" + i.ToString());
SetHead(sheet);
SetCoutent(lists[i], sheet);
}
using (FileStream stream = new FileStream(reportFile, FileMode.Create))
{
hssWorkBook.Write(stream);
}
hssWorkBook.Dispose();
return(reportFile);
}
/// <summary>
/// 向页面输出excel文件
/// 添加人：冯建
/// 添加时间：2012-12-26
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="excelName">Excel的名称</param>
private void WriteToFile(HSSFWorkbook hssfworkbook, string excelName)
{
var rootPath = Server.MapPath("~/UploadFiles/");
var _title = "/" + excelName + ".xls";
if (!string.IsNullOrEmpty(Request.Browser.Browser))
{
if (Request.Browser.Browser.ToLower().IndexOf("ie") >= 0)
{
_title = Server.UrlEncode(_title);
}
}
//Write the stream data of workbook to the root directory
var fs = new FileStream(rootPath + "/" + excelName + ".xls", FileMode.Create, FileAccess.ReadWrite);
hssfworkbook.Write(fs);
hssfworkbook.Dispose();
fs.Close();
//把文件以流方式指定xls格式提供下载
fs = System.IO.File.OpenRead(rootPath + "/" + excelName + ".xls");
var FileArray = new byte[fs.Length];
fs.Read(FileArray, 0, FileArray.Length);
fs.Close();
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + _title);
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Length", FileArray.Length.ToString());
Response.BinaryWrite(FileArray);
Response.Flush();
Response.End();
Response.Clear();
}
/// <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>
/// <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);
}
}
/// <summary>
/// 将单个数据表保存到Excel
/// </summary>
/// <param name="dataTable">数据表</param>
/// <param name="fileName">文件名</param>
public static void ExportExcel(DataTable dataTable, string fileName = null)
{
HSSFWorkbook workBook = new HSSFWorkbook();
Sheet sheet = workBook.CreateSheet("Sheet1");
Font font = workBook.CreateFont();
font.Boldweight = 700;
CellStyle style = workBook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font);
int rownum = 0;
Row row = sheet.CreateRow(rownum++);
Cell cell;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(dataTable.Columns[i].ColumnName);
cell.CellStyle = style;
}
CellStyle dateStyle = workBook.CreateCellStyle();
DataFormat format = workBook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
foreach (DataRow dataRow in dataTable.Rows)
{
row = sheet.CreateRow(rownum++);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
cell = row.CreateCell(i);
string strValue = dataRow[i].ToString();
if (string.IsNullOrWhiteSpace(strValue))
{
cell.SetCellValue("");
}
else
{
switch (dataTable.Columns[i].DataType.ToString())
{
case "System.DateTime":
DateTime dateTime;
DateTime.TryParse(strValue, out dateTime);
cell.SetCellValue(dateTime);
cell.CellStyle = dateStyle;
break;
case "System.Boolean":
bool bValue;
bool.TryParse(strValue, out bValue);
cell.SetCellValue(bValue);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
int iValue = 0;
int.TryParse(strValue, out iValue);
cell.SetCellValue(iValue);
break;
case "System.Decimal":
case "System.Double":
double dValue = 0;
double.TryParse(strValue, out dValue);
cell.SetCellValue(dValue);
break;
default:
cell.SetCellValue(strValue);
break;
}
}
}
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
workBook.Write(HttpContext.Current.Response.OutputStream);
workBook.Dispose();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
if (string.IsNullOrEmpty(fileName))
{
fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
}
HttpContext.Current.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" + GetToExcelName(fileName) + ".xls");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
public void Dispose()
{
_sheet.Dispose();
_workbook.Dispose();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "HJCCL"; //填加xls文件作者信息
si.ApplicationName = "使用NPOI封装类"; //填加xls文件创建程序信息
si.LastAuthor = "努力做一个好人"; //填加xls文件最后保存者信息
si.Comments = "交流学习,多多指教"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = "HJCCL.Utils.Npoi扩展封装"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
var dateStyle = workbook.CreateCellStyle();
var 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 表头及样式
{
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列头及样式
{
var headerRow = sheet.CreateRow(1);
var headStyle = workbook.CreateCellStyle();
var 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);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var 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.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
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();
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
if (Extension == ".xls")
{
//string notxlsx = ("This is not an xlsx file");
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//Counts the number of cells in a row and determines the columns from that.
int counter = sheet.GetRow(0).Cells.Count;
// J < number of columns needs to be exact at this moment
for (int j = 0; j < counter; j++)
{
// set each column to a - ** letters
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//Get first row and set the headers for each cell
//dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
//Get each cell value in row 0 and return its string for a column name.
dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
//Hackish way to remove the bad first row made by getting column names
dt.Rows.RemoveAt(0);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
//Bind the data
GridView1.DataBind();
sheet.Dispose();
hssfworkbook.Dispose();
}
else
{
//Create a new epplus package using openxml
var pck = new OfficeOpenXml.ExcelPackage();
//load the package with the filepath I got from my fileuploader above on the button
//pck.Load(new System.IO.FileInfo(FilePath).OpenRead());
//stream the package
FileStream stream = new FileStream(FilePath, FileMode.Open);
pck.Load(stream);
//So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
//Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
var ws = pck.Workbook.Worksheets[1];
//This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
if (ws == null)
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
// Obiviously I didn't add anything to the sheet so probably can count on it being blank.
}
//I created this datatable for below.
DataTable tbl = new DataTable();
//My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
var hdr = bool.Parse(isHDR);
Console.WriteLine(hdr);
//Set the bool value for from above.
var hasHeader = hdr;
//Setup the table based on the value from my bool
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
//Bind Data to GridView
//I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = tbl;
//Bind the data
GridView1.DataBind();
pck.Save();
pck.Dispose();
stream.Close();
// string pathD = FilePath;
FilePath = null;
stream = null;
// var fileToDelete = new FileInfo(pathD);
// fileToDelete.Delete();
}
}
private static MemoryStream Export2Delivery(DeliveryNote note)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "ChuTian"; //填加xls文件作者信息
si.ApplicationName = "ExcelOrderHelper"; //填加xls文件创建程序信息
si.LastAuthor = "ChuTian"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//CellStyle dateStyle = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
sheet.SetColumnWidth(0, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(1, (int)((18.5 + 0.72) * 256));
sheet.SetColumnWidth(2, (int)((9.5 + 0.72) * 256));
sheet.SetColumnWidth(3, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(4, (int)((12.5 + 0.72) * 256));
sheet.SetColumnWidth(5, (int)((15.5 + 0.72) * 256));
sheet.SetColumnWidth(6, (int)((20 + 0.72) * 256));
//所有的字体
Font font18 = workbook.CreateFont();
font18.FontName = "华文彩云";
font18.FontHeightInPoints = 18;
font18.IsItalic = true;
Font font11Bold = workbook.CreateFont();
font11Bold.FontHeightInPoints = 11;
font11Bold.Boldweight = 700;
Font font11Normal = workbook.CreateFont();
font11Normal.FontHeightInPoints = 11;
Font font20Bold = workbook.CreateFont();
font20Bold.FontHeightInPoints = 20;
font20Bold.Boldweight = 700;
Font foot10Bold = workbook.CreateFont();
foot10Bold.FontHeightInPoints = 10;
foot10Bold.Boldweight = 700;
CellStyle companyStyle = workbook.CreateCellStyle();
companyStyle.Alignment = HorizontalAlignment.CENTER;
companyStyle.SetFont(font18);
Row companyRow = sheet.CreateRow(rowIndex);
companyRow.HeightInPoints = 30;
companyRow.CreateCell(0).SetCellValue(" 湖北楚天通讯材料有限公司");
companyRow.GetCell(0).CellStyle = companyStyle;
rowIndex++;
//创建表头
CellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
titleStyle.SetFont(font20Bold);
Row titleRow = sheet.CreateRow(rowIndex);
titleRow.HeightInPoints = 29;
titleRow.CreateCell(0).SetCellValue(" 送 货 单");
titleRow.GetCell(0).CellStyle = titleStyle;
//创建Logo
string strLogoPath = AppDomain.CurrentDomain.BaseDirectory + "ctlogo.png";
byte[] bLogo = System.IO.File.ReadAllBytes(strLogoPath);
int pictureIdx = workbook.AddPicture(bLogo, PictureType.PNG);
var patriarch = sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 82, 39, 0, 2, 0, 2);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex++;
rowIndex++;
//创建父表内容
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.LEFT;
headStyle.SetFont(font11Bold);
CellStyle headDescStyle = workbook.CreateCellStyle();
headDescStyle.Alignment = HorizontalAlignment.RIGHT;
headDescStyle.SetFont(font11Normal);
Row parentRow1 = sheet.CreateRow(rowIndex);
parentRow1.CreateCell(0).SetCellValue("客户:");
parentRow1.CreateCell(1).SetCellValue(note.customer);
parentRow1.CreateCell(4).SetCellValue("送货单号：");
parentRow1.CreateCell(5).SetCellValue(note.deliverid.ToString().PadLeft(3, '0'));
parentRow1.CreateCell(6).SetCellValue(note.description);
parentRow1.GetCell(0).CellStyle = headStyle;
parentRow1.GetCell(1).CellStyle = headStyle;
parentRow1.GetCell(4).CellStyle = headStyle;
parentRow1.GetCell(5).CellStyle = headStyle;
parentRow1.GetCell(6).CellStyle = headDescStyle;
rowIndex++;
Row parentRow2 = sheet.CreateRow(rowIndex);
parentRow2.CreateCell(0).SetCellValue("型号:");
parentRow2.CreateCell(1).SetCellValue(note.model);
parentRow2.CreateCell(4).SetCellValue("发货时间：");
parentRow2.CreateCell(5).SetCellValue(note.deliverdate.ToString("yyyy.MM.dd"));
parentRow2.GetCell(0).CellStyle = headStyle;
parentRow2.GetCell(1).CellStyle = headStyle;
parentRow2.GetCell(4).CellStyle = headStyle;
parentRow2.GetCell(5).CellStyle = headStyle;
rowIndex++;
Row parentRow3 = sheet.CreateRow(rowIndex);
parentRow3.CreateCell(0).SetCellValue("货物名称:");
parentRow3.CreateCell(1).SetCellValue(note.goodname);
parentRow3.CreateCell(4).SetCellValue("出厂批号：");
parentRow3.CreateCell(5).SetCellValue(note.batch);
parentRow3.GetCell(0).CellStyle = headStyle;
parentRow3.GetCell(1).CellStyle = headStyle;
parentRow3.GetCell(4).CellStyle = headStyle;
parentRow3.GetCell(5).CellStyle = headStyle;
rowIndex++;
//表格样式
CellStyle tableStyle = workbook.CreateCellStyle();
CellStyle tableLeftStyle = workbook.CreateCellStyle();
tableStyle.Alignment = HorizontalAlignment.CENTER;
tableLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableStyle.SetFont(font11Normal);
tableLeftStyle.SetFont(font11Normal);
tableStyle.BorderTop = CellBorderType.THIN;
tableStyle.BorderBottom = CellBorderType.THIN;
tableStyle.BorderLeft = CellBorderType.THIN;
tableStyle.BorderRight = CellBorderType.THIN;
tableLeftStyle.BorderTop = CellBorderType.THIN;
tableLeftStyle.BorderBottom = CellBorderType.THIN;
tableLeftStyle.BorderLeft = CellBorderType.THIN;
tableLeftStyle.BorderRight = CellBorderType.THIN;
//小计和合计样式
CellStyle tableSumStyle = workbook.CreateCellStyle();
CellStyle tableSumLeftStyle = workbook.CreateCellStyle();
tableSumStyle.Alignment = HorizontalAlignment.CENTER;
tableSumLeftStyle.Alignment = HorizontalAlignment.LEFT;
tableSumStyle.SetFont(font11Bold);
tableSumLeftStyle.SetFont(font11Bold);
tableSumStyle.BorderTop = CellBorderType.THIN;
tableSumStyle.BorderBottom = CellBorderType.THIN;
tableSumStyle.BorderLeft = CellBorderType.THIN;
tableSumStyle.BorderRight = CellBorderType.THIN;
tableSumLeftStyle.BorderTop = CellBorderType.THIN;
tableSumLeftStyle.BorderBottom = CellBorderType.THIN;
tableSumLeftStyle.BorderLeft = CellBorderType.THIN;
tableSumLeftStyle.BorderRight = CellBorderType.THIN;
Row colHeaderRow = sheet.CreateRow(rowIndex);
colHeaderRow.CreateCell(0).SetCellValue("件号");
colHeaderRow.CreateCell(1).SetCellValue("规格");
colHeaderRow.CreateCell(2).SetCellValue("盘数");
colHeaderRow.CreateCell(3).SetCellValue("净重（KG)");
colHeaderRow.CreateCell(4).SetCellValue("单价");
colHeaderRow.CreateCell(5).SetCellValue("金额");
colHeaderRow.CreateCell(6).SetCellValue("合同号");
colHeaderRow.CreateCell(7).SetCellValue("毛重");
colHeaderRow.CreateCell(8).SetCellValue("管芯重量");
colHeaderRow.GetCell(0).CellStyle = tableLeftStyle;
colHeaderRow.GetCell(1).CellStyle = tableStyle;
colHeaderRow.GetCell(2).CellStyle = tableStyle;
colHeaderRow.GetCell(3).CellStyle = tableStyle;
colHeaderRow.GetCell(4).CellStyle = tableStyle;
colHeaderRow.GetCell(5).CellStyle = tableStyle;
colHeaderRow.GetCell(6).CellStyle = tableStyle;
colHeaderRow.GetCell(7).CellStyle = tableStyle;
colHeaderRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
Dictionary <string, List <DeliveryItem> > dicDelivery = DeliveryItemGroup(note.items);
int iDisoTotal = 0;
double dWeightTotal = 0;
double dPriceTotal = 0;
foreach (string delivertyId in dicDelivery.Keys)
{
int iDisoSum = 0;
double dWeightSum = 0;
double dPriceSum = 0;
List <DeliveryItem> noteItems = dicDelivery[delivertyId];
foreach (DeliveryItem item in noteItems)
{
iDisoSum = iDisoSum + item.discnum;
dWeightSum = dWeightSum + item.weight;
dPriceSum = dPriceSum + item.totalprice;
iDisoTotal = iDisoTotal + item.discnum;
dWeightTotal = dWeightTotal + item.weight;
dPriceTotal = dPriceTotal + item.totalprice;
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue(item.jiannum);
colItemRow.GetCell(0).CellStyle = tableLeftStyle;
colItemRow.CreateCell(1).SetCellValue(item.specifications + "*" + item.lenght.ToString());
colItemRow.GetCell(1).CellStyle = tableStyle;
colItemRow.CreateCell(2).SetCellValue(item.discnum);
colItemRow.GetCell(2).CellStyle = tableStyle;
colItemRow.CreateCell(3).SetCellValue(item.weight.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableStyle;
colItemRow.CreateCell(4).SetCellValue(item.price.ToString("f5"));
colItemRow.GetCell(4).CellStyle = tableStyle;
colItemRow.CreateCell(5).SetCellValue(item.totalprice.ToString("f5"));
colItemRow.GetCell(5).CellStyle = tableStyle;
colItemRow.CreateCell(6).SetCellValue(item.contractno);
colItemRow.GetCell(6).CellStyle = tableStyle;
colItemRow.CreateCell(7).SetCellValue(item.netweight);
colItemRow.GetCell(7).CellStyle = tableStyle;
colItemRow.CreateCell(8).SetCellValue(item.coreweight);
colItemRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
}
if (noteItems.Count > 1 && dicDelivery.Count > 1)
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("小计");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue(iDisoSum.ToString());
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue(dWeightSum.ToString("f2"));
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue(dPriceSum.ToString("f2"));
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
else
{
Row colItemRow = sheet.CreateRow(rowIndex);
colItemRow.CreateCell(0).SetCellValue("");
colItemRow.GetCell(0).CellStyle = tableSumLeftStyle;
colItemRow.CreateCell(1).SetCellValue("");
colItemRow.GetCell(1).CellStyle = tableSumStyle;
colItemRow.CreateCell(2).SetCellValue("");
colItemRow.GetCell(2).CellStyle = tableSumStyle;
colItemRow.CreateCell(3).SetCellValue("");
colItemRow.GetCell(3).CellStyle = tableSumStyle;
colItemRow.CreateCell(4).SetCellValue("");
colItemRow.GetCell(4).CellStyle = tableSumStyle;
colItemRow.CreateCell(5).SetCellValue("");
colItemRow.GetCell(5).CellStyle = tableSumStyle;
colItemRow.CreateCell(6).SetCellValue("");
colItemRow.GetCell(6).CellStyle = tableSumStyle;
colItemRow.CreateCell(7).SetCellValue("");
colItemRow.GetCell(7).CellStyle = tableSumStyle;
colItemRow.CreateCell(8).SetCellValue("");
colItemRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
}
}
//空行
Row colBlankRow = sheet.CreateRow(rowIndex);
colBlankRow.CreateCell(0).SetCellValue("");
colBlankRow.GetCell(0).CellStyle = tableLeftStyle;
colBlankRow.CreateCell(1).SetCellValue("");
colBlankRow.GetCell(1).CellStyle = tableStyle;
colBlankRow.CreateCell(2).SetCellValue("");
colBlankRow.GetCell(2).CellStyle = tableStyle;
colBlankRow.CreateCell(3).SetCellValue("");
colBlankRow.GetCell(3).CellStyle = tableStyle;
colBlankRow.CreateCell(4).SetCellValue("");
colBlankRow.GetCell(4).CellStyle = tableStyle;
colBlankRow.CreateCell(5).SetCellValue("");
colBlankRow.GetCell(5).CellStyle = tableStyle;
colBlankRow.CreateCell(6).SetCellValue("");
colBlankRow.GetCell(6).CellStyle = tableStyle;
colBlankRow.CreateCell(7).SetCellValue("");
colBlankRow.GetCell(7).CellStyle = tableStyle;
colBlankRow.CreateCell(8).SetCellValue("");
colBlankRow.GetCell(8).CellStyle = tableStyle;
rowIndex++;
//合计
Row colTotalRow = sheet.CreateRow(rowIndex);
colTotalRow.CreateCell(0).SetCellValue("合计");
colTotalRow.GetCell(0).CellStyle = tableSumLeftStyle;
colTotalRow.CreateCell(1).SetCellValue("");
colTotalRow.GetCell(1).CellStyle = tableSumStyle;
colTotalRow.CreateCell(2).SetCellValue(iDisoTotal.ToString());
colTotalRow.GetCell(2).CellStyle = tableSumStyle;
colTotalRow.CreateCell(3).SetCellValue(dWeightTotal.ToString("f2"));
colTotalRow.GetCell(3).CellStyle = tableSumStyle;
colTotalRow.CreateCell(4).SetCellValue("");
colTotalRow.GetCell(4).CellStyle = tableSumStyle;
colTotalRow.CreateCell(5).SetCellValue(dPriceTotal.ToString("f2"));
colTotalRow.GetCell(5).CellStyle = tableSumStyle;
colTotalRow.CreateCell(6).SetCellValue("");
colTotalRow.GetCell(6).CellStyle = tableSumStyle;
colTotalRow.CreateCell(7).SetCellValue("");
colTotalRow.GetCell(7).CellStyle = tableSumStyle;
colTotalRow.CreateCell(8).SetCellValue("");
colTotalRow.GetCell(8).CellStyle = tableSumStyle;
rowIndex++;
CellStyle footStyle = workbook.CreateCellStyle();
footStyle.Alignment = HorizontalAlignment.LEFT;
footStyle.SetFont(font11Normal);
CellStyle footBoldStyle = workbook.CreateCellStyle();
footBoldStyle.Alignment = HorizontalAlignment.LEFT;
footBoldStyle.SetFont(font11Bold);
CellStyle footRightStyle = workbook.CreateCellStyle();
footRightStyle.Alignment = HorizontalAlignment.RIGHT;
footRightStyle.SetFont(font11Normal);
Row descRow = sheet.CreateRow(rowIndex);
descRow.CreateCell(0).SetCellValue("备注：");
descRow.GetCell(0).CellStyle = footBoldStyle;
descRow.CreateCell(1).SetCellValue(note.description1);
descRow.GetCell(1).CellStyle = footBoldStyle;
rowIndex++;
Row footerRow = sheet.CreateRow(rowIndex);
footerRow.CreateCell(0).SetCellValue("请按上列货验收");
footerRow.GetCell(0).CellStyle = footStyle;
rowIndex++;
Row footerRow1 = sheet.CreateRow(rowIndex);
footerRow1.CreateCell(0).SetCellValue("收货人:");
footerRow1.GetCell(0).CellStyle = footStyle;
footerRow1.CreateCell(1).SetCellValue("");
footerRow1.GetCell(1).CellStyle = footStyle;
footerRow1.CreateCell(2).SetCellValue("送货人:");
footerRow1.GetCell(2).CellStyle = footStyle;
footerRow1.CreateCell(3).SetCellValue("");
footerRow1.GetCell(3).CellStyle = footRightStyle;
footerRow1.CreateCell(4).SetCellValue("制单:");
footerRow1.GetCell(4).CellStyle = footRightStyle;
footerRow1.CreateCell(5).SetCellValue(note.loginid);
footerRow1.GetCell(5).CellStyle = footStyle;
footerRow1.CreateCell(6).SetCellValue("审核：");
footerRow1.GetCell(6).CellStyle = footStyle;
rowIndex++;
CellStyle footStyle1 = workbook.CreateCellStyle();
footStyle1.Alignment = HorizontalAlignment.LEFT;
footStyle1.SetFont(foot10Bold);
Row footerRow2 = sheet.CreateRow(rowIndex);
footerRow2.CreateCell(0).SetCellValue("地址：湖北省汉川市马口工业园区楚天路");
footerRow2.GetCell(0).CellStyle = footStyle1;
footerRow2.CreateCell(1).SetCellValue("");
footerRow2.GetCell(1).CellStyle = footStyle1;
footerRow2.CreateCell(2).SetCellValue("");
footerRow2.GetCell(2).CellStyle = footStyle1;
footerRow2.CreateCell(3).SetCellValue(" 电话：0712-8521088 传真：0712-8512311");
footerRow2.GetCell(3).CellStyle = footStyle1;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}
//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);
}
}
// 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();
}
}
