public void SequenceTest()
{
var id1 = SequenceGuid.NewGuid();
var id2 = SequenceGuid.NewGuid();
Assert.True(id2.CompareTo(id1) > 0);
output.WriteLine(id1.ToString());
output.WriteLine(id2.ToString());
}
public void SerializeTo(Stream stream)
{
var bw = new BinaryWriter(stream);
bw.Write(PacketId);
bw.Write(FullSequenceSize);
bw.Write(PartsCount);
bw.Write(SequenceGuid.ToByteArray());
bw.Write(IsOperation);
bw.Write(OperationGuid.ToByteArray());
}
/// <summary>
/// 通过NPOI，读模版表格，生成报表(针对不规则表格处理)
/// </summary>
/// <param name="fileName">报表名称</param>
/// <param name="context">上下文件</param>
/// <param name="parms">第几行几列存储的内容,格式:1|1</param>
/// <param name="templatePath">模版文件</param>
/// <param name="filePath">生成后的文件位置</param>
/// <returns></returns>
public static bool ExportExcel(string fileName, HttpContext context, //
Dictionary <string, object> parms, string templatePath, out string filePath)
{
InitializeWorkbook(context, templatePath);
var sheet = _hssfworkbook.GetSheetAt(0);
foreach (var o in parms)
{
var key = o.Key;
var value = o.Value.ToString();
var row = Convert.ToInt32(key.Split('|')[0]);
var col = Convert.ToInt32(key.Split('|')[1]);
var dataRow = sheet.GetRow(row);
var newCell = dataRow.GetCell(col);
if (newCell == null)
{
continue;
}
newCell.SetCellValue(value);
}
var sequence = SequenceGuid.GetGuid();
//上传文件路径
var uploadpath = ConfigurationManager.AppSettings["上传文件路径"];
//该目录设定死，最好不要修改
var fileTemp = (string.Format(@"{0}/temp/{1}/", uploadpath, DateTime.Now.ToString("yyyy-MM-dd"))); //
if (!DirFile.XFileExists(context.Server.MapPath(fileTemp)))
{
DirFile.XCreateDir(context.Server.MapPath(fileTemp));
}
//
uploadpath = string.Format(@"{0}/{1}{2}.xls", fileTemp, fileName, sequence);
//创建文件
using (var fileStream = new FileStream(context.Server.MapPath(uploadpath), FileMode.Create))
{
_hssfworkbook.Write(fileStream);
fileStream.Close();
}
filePath = uploadpath;
return(true);
}
/// <summary>
/// 导出本页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format(@"attachment; filename=空瓶出库单{0}.xls", SequenceGuid.GetGuidReplace()));
Response.ContentType = "application/excel";
Response.ContentEncoding = Encoding.UTF8;
Response.Write(Utils.GetGridTableHtml(Grid1));
Response.End();
}
/// <summary>
/// 部门树加载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void trDept_NodeCommand(object sender, TreeCommandEventArgs e)
{
BindDataGrid();
if (trDept.SelectedNodeID != "1")
{
btnAdd.OnClientClick = Window1.GetShowReference(string.Format("./ParsEdit.aspx?action=1&FSubCateId={0}×tamp={1}",
trDept.SelectedNodeID, SequenceGuid.GetGuidReplace()), "添加产品");
}
}
/// <summary>
/// 页面初始化
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetPermissionButtons(Toolbar1);
//产品分类
LoadTreeSource();
btnAdd.OnClientClick = Window1.GetShowReference(string.Format("./ParsEdit.aspx?action=1&FSubCateId={0}×tamp={1}",
trDept.SelectedNodeID, SequenceGuid.GetGuidReplace()), "添加产品");
//btnBatchDelete.ConfirmText = "你确定要执行删除操作吗？";
BindDataGrid();
}
}
/// <summary>
/// 通过NPOI，读模版表格，生成报表
/// </summary>
/// <param name="fileName">报表名称</param>
/// <param name="rowIndex">从模版中的第几行插入操作</param>
/// <param name="httpContext">上下文件</param>
/// <param name="dataSource">数据源</param>
/// <param name="templatePath">模版文件</param>
/// <param name="filePath">生成后的文件位置</param>
/// <returns></returns>
public static bool ExportExcel(string fileName, Int32 rowIndex, HttpContext httpContext, //
DataTable dataSource, string templatePath, out string filePath)
{
InitializeWorkbook(httpContext, templatePath);
var sheet = _hssfworkbook.GetSheetAt(0);
try
{
foreach (DataRow row in dataSource.Rows)
{
#region 填充内容
var iRow = sheet.GetRow(rowIndex);
foreach (DataColumn column in dataSource.Columns)
{
if (null == iRow)
{
continue;
}
var iCell = iRow.GetCell(column.Ordinal);
if (null == iCell)
{
continue;
}
string rowValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
iCell.SetCellValue(rowValue);
break;
case "System.DateTime": //日期类型
DateTime valueTime;
DateTime.TryParse(rowValue, out valueTime);
iCell.SetCellValue(valueTime.ToString());
break;
case "System.Boolean": //布尔型
bool value;
bool.TryParse(rowValue, out value);
iCell.SetCellValue(value);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue;
int.TryParse(rowValue, out intValue);
iCell.SetCellValue(intValue);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubValue;
double.TryParse(rowValue, out doubValue);
iCell.SetCellValue(doubValue);
break;
case "System.DBNull": //空值处理
iCell.SetCellValue("");
break;
default:
iCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
catch (Exception ex)
{
throw ex;
}
sheet.ForceFormulaRecalculation = true;
//生成sequence
var sequence = SequenceGuid.GetGuid();
//上传文件路径
var uploadpath = ConfigurationManager.AppSettings["上传文件路径"];
//该目录设定死，最好不要修改
var fileTemp = (string.Format(@"{0}/temp/{1}/", uploadpath, DateTime.Now.ToString("yyyy-MM-dd"))); //
//目录不存在，生成新文件夹
if (!DirFile.XFileExists(httpContext.Server.MapPath(fileTemp)))
{
DirFile.XCreateDir(httpContext.Server.MapPath(fileTemp));
}
//拼接生成后的文件路径
uploadpath = string.Format(@"{0}/{1}{2}.xls", fileTemp, fileName, sequence);
//创建文件
using (var fileStream = new FileStream(httpContext.Server.MapPath(uploadpath), FileMode.Create))
{
//通过流方式写入到EXCEL中
_hssfworkbook.Write(fileStream);
//关闭输入流
fileStream.Close();
}
//输出生成后的文件路径
filePath = uploadpath;
return(true);
}
/// <summary>
/// 通过NPOI，读模版表格，生成报表(针对不规则表格处理)
/// </summary>
/// <param name="fileName">报表名称</param>
/// <param name="context">上下文件</param>
/// <param name="parms">第几行几列存储的内容,格式:1|1</param>
/// <param name="templatePath">模版文件</param>
/// <param name="filePath">生成后的文件位置</param>
/// <returns></returns>
public static bool ExportExcel2(string fileName, Int32 rowIndex, HttpContext context, //
Dictionary <string, object> parms, DataTable dataSource, string templatePath, out string filePath)
{
InitializeWorkbook(context, templatePath);
var sheet = _hssfworkbook.GetSheetAt(0);
//指定单元格
foreach (var o in parms)
{
var key = o.Key;
var value = o.Value.ToString();
var row = Convert.ToInt32(key.Split('|')[0]);
var col = Convert.ToInt32(key.Split('|')[1]);
var dataRow = sheet.GetRow(row);
var newCell = dataRow.GetCell(col);
if (newCell == null)
{
continue;
}
newCell.SetCellValue(value);
}
//指定行写入
foreach (DataRow row in dataSource.Rows)
{
#region 填充内容
var iRow = sheet.GetRow(rowIndex);
foreach (DataColumn column in dataSource.Columns)
{
if (null == iRow)
{
continue;
}
var iCell = iRow.GetCell(column.Ordinal);
if (null == iCell)
{
continue;
}
string rowValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
iCell.SetCellValue(rowValue);
break;
case "System.DateTime": //日期类型
DateTime valueTime;
DateTime.TryParse(rowValue, out valueTime);
iCell.SetCellValue(valueTime.ToString());
break;
case "System.Boolean": //布尔型
bool value;
bool.TryParse(rowValue, out value);
iCell.SetCellValue(value);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intValue;
int.TryParse(rowValue, out intValue);
iCell.SetCellValue(intValue);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubValue;
double.TryParse(rowValue, out doubValue);
iCell.SetCellValue(doubValue);
break;
case "System.DBNull": //空值处理
iCell.SetCellValue("");
break;
default:
iCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
var sequence = SequenceGuid.GetGuid();
//上传文件路径
var uploadpath = ConfigurationManager.AppSettings["上传文件路径"];
//该目录设定死，最好不要修改
var fileTemp = (string.Format(@"{0}/temp/{1}/", uploadpath, DateTime.Now.ToString("yyyy-MM-dd"))); //
if (!DirFile.XFileExists(context.Server.MapPath(fileTemp)))
{
DirFile.XCreateDir(context.Server.MapPath(fileTemp));
}
//
uploadpath = string.Format(@"{0}/{1}{2}.xls", fileTemp, fileName, sequence);
//创建文件
using (var fileStream = new FileStream(context.Server.MapPath(uploadpath), FileMode.Create))
{
_hssfworkbook.Write(fileStream);
fileStream.Close();
}
filePath = uploadpath;
return(true);
}
/// <summary>
/// 把指定的DataTable导出Excel
/// Yakecan
/// </summary>
/// <param name="httpContext"></param>
/// <param name="dataTable">数据源</param>
/// <param name="filename">Sheet的名称</param>
/// <param name="fls">返回生成的文件路径</param>
public static void Export(HttpContext httpContext, DataTable dataTable, string filename, out string fls)
{
HSSFWorkbook workbook = InitializeWorkBook();
ISheet sheet1 = workbook.CreateSheet(filename);
IRow titleRow = sheet1.CreateRow(0);
titleRow.Height = (short)20 * 25;
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = (short)16;
titleStyle.SetFont(font);
var region = new CellRangeAddress(0, 0, 0, dataTable.Columns.Count);
sheet1.AddMergedRegion(region);
var titleCell = titleRow.CreateCell(0);
titleCell.CellStyle = titleStyle;
titleCell.SetCellValue(filename);
IRow headerRow = sheet1.CreateRow(1);
var headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = (short)11;
titleFont.FontName = "宋体";
headerStyle.SetFont(titleFont);
headerRow.CreateCell(0).SetCellValue("序号");
headerRow.GetCell(0).CellStyle = headerStyle;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
headerRow.CreateCell(i + 1).SetCellValue(dataTable.Columns[i].ColumnName);
headerRow.GetCell(i + 1).CellStyle = headerStyle;
sheet1.SetColumnWidth(i, 256 * 18);
}
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.BorderBottom = BorderStyle.Thin;
bodyStyle.BorderLeft = BorderStyle.Thin;
bodyStyle.BorderRight = BorderStyle.Thin;
bodyStyle.BorderTop = BorderStyle.Thin;
for (int r = 0; r < dataTable.Rows.Count; r++)
{
IRow bodyRow = sheet1.CreateRow(r + 2);
bodyRow.CreateCell(0).SetCellValue(r + 1);
bodyRow.GetCell(0).CellStyle = bodyStyle;
bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Left;
for (int c = 0; c < dataTable.Columns.Count; c++)
{
bodyRow.CreateCell(c + 1).SetCellValue(dataTable.Rows[r][c].ToString());
bodyRow.GetCell(c + 1).CellStyle = bodyStyle;
}
}
var sequence = SequenceGuid.GetGuid();
var files = (string.Format(@"~/upload/temp/{0}/", DateTime.Now.ToString("yyyy-MM-dd"))); //
if (!DirFile.XFileExists(httpContext.Server.MapPath(files)))
{
DirFile.XCreateDir(httpContext.Server.MapPath(files));
}
var uploadpath = files + sequence + ".xls";
//创建文件
using (var fileStream = new FileStream(httpContext.Server.MapPath(uploadpath), FileMode.Create))
{
workbook.Write(fileStream);
fileStream.Close();
}
//返回生成文件
fls = uploadpath;
}
