public static NPOI.SS.UserModel.ICellStyle GetCellStyle(this NPOI.SS.UserModel.IWorkbook workbook, short backColorIndex, short fontColorIndex, System.Drawing.Font font, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General, NPOI.SS.UserModel.VerticalAlignment verticalAlignment = NPOI.SS.UserModel.VerticalAlignment.None, NPOI.SS.UserModel.BorderStyle borderLeft = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderTop = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderRight = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderBottom = NPOI.SS.UserModel.BorderStyle.None)
{
NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle();
if (backColorIndex >= 8 && backColorIndex <= 63)
{
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.FillForegroundColor = backColorIndex;
}
cellStyle.Alignment = horizontalAlignment;
cellStyle.VerticalAlignment = verticalAlignment;
cellStyle.BorderLeft = borderLeft;
cellStyle.BorderTop = borderTop;
cellStyle.BorderRight = borderRight;
cellStyle.BorderBottom = borderBottom;
if (font != null)
{
NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont();
if (fontColorIndex >= 8 && fontColorIndex <= 63)
{
cellFont.Color = fontColorIndex;
}
cellFont.FontName = font.Name;
cellFont.FontHeight = font.Size;
cellFont.Boldweight = (short)(font.Bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : NPOI.SS.UserModel.FontBoldWeight.Normal);
cellStyle.SetFont(cellFont);
}
return(cellStyle);
}
/// <summary>
/// 设置表头样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel HeadStyle(IWorkSheet table, CellStyle style)
{
if (_headStyle == null)
{
_headStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(0, table.HeadRowCount - 1, 0, table.ColumnCount - 1, _headStyle);
return(this);
}
/// <summary>
/// 设置页脚样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel FootStyle(IWorkSheet table, CellStyle style)
{
if (_footStyle == null)
{
_footStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(table.HeadRowCount + table.BodyRowCount, table.RowCount - 1, 0, table.ColumnCount - 1, _footStyle);
return(this);
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public static void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
/// <summary>
/// 创建日期样式
/// </summary>
/// <returns></returns>
private NPOI.SS.UserModel.ICellStyle CreateDateStyle()
{
if (_dateStyle != null)
{
return(_dateStyle);
}
_dateStyle = CellStyleResolver.Resolve(_workbook, CellStyle.Body());
var format = _workbook.CreateDataFormat();
_dateStyle.DataFormat = format.GetFormat(_dateFormat);
return(_dateStyle);
}
/// <summary>
/// 设置样式
/// </summary>
/// <param name="startRowIndex">起始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="startColumnIndex">起始列索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
protected IExcel Style(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex,
NPOI.SS.UserModel.ICellStyle style)
{
for (var i = startRowIndex; i <= endRowIndex; i++)
{
var row = GetOrCreateRow(i);
for (var j = startColumnIndex; j <= endColumnIndex; j++)
{
GetOrCreateCell(row, j).CellStyle = style;
}
}
return(this);
}
private NPOI.SS.UserModel.ICellStyle CloneStyle()
{
if (myStyle == null)
{
myStyle = this._cell.Sheet.Workbook.CreateCellStyle();
myStyle.BorderBottom = this._cell.CellStyle.BorderBottom;
myStyle.BorderLeft = this._cell.CellStyle.BorderLeft;
myStyle.BorderRight = this._cell.CellStyle.BorderRight;
myStyle.BorderTop = this._cell.CellStyle.BorderTop;
myStyle.FillForegroundColor = this._cell.CellStyle.FillForegroundColor;
myStyle.FillPattern = this._cell.CellStyle.FillPattern;
}
return(myStyle);
}
protected void btnExport_Click(object sender, EventArgs e)
{
DateTime start = Common.St.ToDateTime(txtStart.Value + " 00:00:00");
DateTime end = Common.St.ToDateTime(txtEnd.Value + " 23:59:59");
var list = DAL.WorkPlanRule.Get(start, end);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_2.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("周上线记录");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
NPOI.SS.UserModel.ICell cell = row.GetCell(0);
cell.SetCellValue("技术测试周上线记录（" + start.ToString("yyyy年MM月dd日") + "-" + end.ToString("yyyy年MM月dd日") + "）");
// 内容
int i = 2;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(o.SheepNo);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.Project.Name);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue(Common.St.ToDateTimeString(o.PublishTime, "yyyy-MM-dd"));
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("每周项目上线记录", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue)
{
if (iCell == null)
{
return;
}
if (cellValue != null)
{
if (cellValue.GetType() == typeof(DBNull))
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else if (cellValue.GetType() == typeof(bool))
{
iCell.SetCellValue((bool)cellValue);
}
else if (cellValue.GetType() == typeof(string))
{
if (cellValue.ToString() == "#NUM!")
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else
{
iCell.SetCellValue((string)cellValue);
}
}
else if (cellValue.GetType() == typeof(DateTime))
{
NPOI.SS.UserModel.ICellStyle iCellStyle = iCell.Sheet.Workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat();
iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
iCell.CellStyle = iCellStyle;
iCell.SetCellValue((DateTime)cellValue);
}
else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float))
{
iCell.SetCellValue((double)cellValue);
}
else
{
iCell.SetCellValue(cellValue.ToString());
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
var list = GetList().AsEnumerable().Select(a => new { Name = GetBugzillaUserName(a.Field <int>("reporter")), CC = a.Field <Int64>("cc") }).Where(a => a.Name != "");
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_5.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("bug统计");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 内容
int i = 2;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.CC.ToString());
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("Bug数统计", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
static void CopyStyleFromCell(NPOI.SS.UserModel.ICellStyle To, NPOI.SS.UserModel.ICell Cell)
{
To.Alignment = Cell.CellStyle.Alignment;
To.BorderBottom = Cell.CellStyle.BorderBottom;
To.BorderLeft = Cell.CellStyle.BorderLeft;
To.BorderRight = Cell.CellStyle.BorderRight;
To.BorderTop = Cell.CellStyle.BorderTop;
To.BottomBorderColor = Cell.CellStyle.BottomBorderColor;
To.FillBackgroundColor = Cell.CellStyle.FillBackgroundColor;
To.FillForegroundColor = Cell.CellStyle.FillForegroundColor;
To.FillPattern = Cell.CellStyle.FillPattern;
To.SetFont(Cell.CellStyle.GetFont(Cell.Row.Sheet.Workbook));
To.IsHidden = Cell.CellStyle.IsHidden;
To.IsLocked = Cell.CellStyle.IsLocked;
To.LeftBorderColor = Cell.CellStyle.LeftBorderColor;
To.RightBorderColor = Cell.CellStyle.RightBorderColor;
To.Rotation = Cell.CellStyle.Rotation;
To.ShrinkToFit = Cell.CellStyle.ShrinkToFit;
To.TopBorderColor = Cell.CellStyle.TopBorderColor;
To.VerticalAlignment = Cell.CellStyle.VerticalAlignment;
To.WrapText = Cell.CellStyle.WrapText;
To.DataFormat = Cell.CellStyle.DataFormat;
}
protected void ExportAward_Click(object sender, EventArgs e)
{
int iPrizeID = 0;
int.TryParse(hfPrizeID.Value, out iPrizeID);
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
List <Dal.Models.AwardResult> list = BLL.Vote.GetAwardResultReport(iPrizeID, conn);
Dal.Models.Prize prize = BLL.Prize.GetPrize(iPrizeID, conn);
string ReportFileName = System.Web.HttpContext.Current.Server.MapPath("~/Content/Temp/ExcelTemp/投票结果_" + prize.PrizeName + ".xls");
try
{
NPOI.HSSF.UserModel.HSSFWorkbook wk = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet ws = wk.CreateSheet("投票结果");
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICellStyle style = wk.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
row = ws.CreateRow(0);
row.HeightInPoints = 35;
SetNewCell(row, 0, "排名", style);
//增加同名奖内部排序
SetNewCell(row, 1, "同名奖内部排序", style);
SetNewCell(row, 2, "项目名称", style);
SetNewCell(row, 3, "申报单位", style);
//SetNewCell(row, 3, "申报奖项", style);
SetNewCell(row, 4, "获奖结果", style);
ws.SetColumnWidth(0, 2000);
//设置同名奖内部排序列宽
ws.SetColumnWidth(1, 3500);
ws.SetColumnWidth(2, 10000);
ws.SetColumnWidth(3, 10000);
ws.SetColumnWidth(4, 4000);
for (int i = 0; i < list.Count(); i++)
{
row = ws.CreateRow(i + 1);
SetNewCell(row, 0, list[i].Ordinal.ToString(), style);
//写入同名奖内部排序
SetNewCell(row, 1, list[i].InnerOrdinal.ToString(), style);
SetNewCell(row, 2, list[i].DeclarationName.ToString(), style);
SetNewCell(row, 3, list[i].OrganizationName.ToString(), style);
//SetNewCell(row, 3, list[i].PrizeName.ToString(), style);
//SetNewCell(row, 3, BLL.Vote.GetAwardsStatus(list[i].VoteCode.ToString(), list[i].PrizeLevelCode), style);
SetNewCell(row, 4, list[i].AwardCodeText, style);
}
//添加专家签名
row = ws.CreateRow(list.Count() + 2);
SetNewCell(row, 1, "专家签名：");
//添加日期
row = ws.CreateRow(list.Count() + 6);
SetNewCell(row, 3, "日期：");
ws.ForceFormulaRecalculation = true;
//先删除上一个临时文件
if (File.Exists(ReportFileName))
{
FileInfo fi = new FileInfo(ReportFileName);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
File.Delete(ReportFileName);
}
//创建临时文件写入数据
using (FileStream filess = File.OpenWrite(ReportFileName))
{
wk.Write(filess);
}
}
catch (Exception ex)
{
Response.Write(JsonConvert.SerializeObject(ex.Message));
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
return;
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=AwardResult_" + DateTime.Now.ToFileTime().ToString() + ".xls");
Response.AddHeader("Content-Length", filet.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(filet.FullName);
Response.End();
}
public static void CreateExcelColorFile(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
// 创建Workbook。
NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook();
if (fileInfo.Extension == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else if (fileInfo.Extension == ".xls")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
// 创建Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor");
// 设置列宽。
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
// 创建标题。
int rowIndex = 0;
NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0);
cell.SetCellValue("Excel颜色");
cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold));
// 空一行
rowIndex++;
// 创建标题行
rowIndex++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
cell = row.CreateCell(0);
cell.SetCellValue("索引");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("索引（NPOI）");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("名称");
cell.CellStyle = columnHeadCellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3));
cell = row.CreateCell(4);
cell.SetCellValue("十六进制代码");
cell.CellStyle = columnHeadCellStyle;
ExcelColor.KnownColors.ForEach(x =>
{
rowIndex++;
NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex);
contentRow.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0);
contentCell.SetCellValue(x.Index);
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(1);
contentCell.SetCellValue(x.IndexNPOI + "（NPOI）");
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(2);
contentCell.SetCellValue(x.Name);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(3);
contentCell.SetCellValue(x.Description);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(4);
contentCell.SetCellValue(x.HexString);
contentCell.CellStyle = contentCellStyle;
});
// 保存到文件。
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite);
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
}
protected void btnExport_Click(object sender, EventArgs e)
{
int year = Common.St.ToInt32(selYear.Value);
var list = DAL.WorkPlanRule.Get();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_3.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("项目月测试次数");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
style.FillPattern = NPOI.SS.UserModel.FillPattern.AltBars;
NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle();
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style2.WrapText = true;
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// 内容
int i = 0;
int a1, a2, a3, a4, a11 = 0, a22 = 0, a33 = 0, a44 = 0;
var userlist = DAL.UserRule.Get();
for (int j = 1; j <= 12; j++)
{
a3 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).Count(); //上线次数
a4 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).GroupBy(a => a.ProjectId).Count(); //上线项目数
string t = year + "-" + (j + 1) + "-1";
if (j == 12)
{
t = (year + 1) + "-1-1";
}
a1 = list.Where(a => (a.State == 1 && a.RealStartTime < DateTime.Parse(t)) || (a.State == 2 && (!(a.RealStartTime >= DateTime.Parse(t) || a.RealEndTime < DateTime.Parse(year + "-" + j + "-1"))))).Count(); //工单数 项目数
if (year == DateTime.Today.Year && j > DateTime.Today.Month)
{
a1 = 0;
a2 = 0;
}
else
{
a2 = userlist.Where(a => a.Status == 1 || a.LeaveTime.Year * 12 + a.LeaveTime.Month > year * 12 + j).Count(); //测试用户数
}
//第一行
NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell00 = row0.CreateCell(0);
cell00.CellStyle = style;
cell00.SetCellValue(GetMonthName(j));
NPOI.SS.UserModel.ICell cell01 = row0.CreateCell(1);
cell01.CellStyle = style;
NPOI.SS.UserModel.ICell cell02 = row0.CreateCell(2);
cell02.CellStyle = style;
NPOI.SS.UserModel.ICell cell03 = row0.CreateCell(3);
cell03.CellStyle = style;
NPOI.SS.UserModel.ICell cell04 = row0.CreateCell(4);
cell04.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4));
//第二行
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell10 = row1.CreateCell(0);
cell10.CellStyle = style;
cell10.SetCellValue("工单数");
NPOI.SS.UserModel.ICell cell11 = row1.CreateCell(1);
cell11.CellStyle = style;
cell11.SetCellValue("项目数");
NPOI.SS.UserModel.ICell cell12 = row1.CreateCell(2);
cell12.CellStyle = style;
cell12.SetCellValue("测试人数");
NPOI.SS.UserModel.ICell cell13 = row1.CreateCell(3);
cell13.CellStyle = style;
cell13.SetCellValue("上线次数");
NPOI.SS.UserModel.ICell cell14 = row1.CreateCell(4);
cell14.CellStyle = style;
cell14.SetCellValue("上线项目数");
//第三行
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell20 = row2.CreateCell(0);
cell20.CellStyle = style2;
cell20.SetCellValue(a1);
NPOI.SS.UserModel.ICell cell21 = row2.CreateCell(1);
cell21.CellStyle = style2;
cell21.SetCellValue(a1);
NPOI.SS.UserModel.ICell cell22 = row2.CreateCell(2);
cell22.CellStyle = style2;
cell22.SetCellValue(a2);
NPOI.SS.UserModel.ICell cell23 = row2.CreateCell(3);
cell23.CellStyle = style2;
cell23.SetCellValue(a3);
NPOI.SS.UserModel.ICell cell24 = row2.CreateCell(4);
cell24.CellStyle = style2;
cell24.SetCellValue(a4);
i++;
a11 += a1; a22 += a2; a33 += a3; a44 += a4;
}
//第一行
NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0);
cell40.CellStyle = style;
cell40.SetCellValue(year + "年总和统计");
NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1);
cell41.CellStyle = style;
NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2);
cell42.CellStyle = style;
NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3);
cell43.CellStyle = style;
NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4);
cell44.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4));
//第二行
NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0);
cell50.CellStyle = style;
cell50.SetCellValue("工单数");
NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1);
cell51.CellStyle = style;
cell51.SetCellValue("项目数");
NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2);
cell52.CellStyle = style;
cell52.SetCellValue("测试人数");
NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3);
cell53.CellStyle = style;
cell53.SetCellValue("上线次数");
NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4);
cell54.CellStyle = style;
cell54.SetCellValue("上线项目数");
//第三行
NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(i++);
NPOI.SS.UserModel.ICell cell60 = row6.CreateCell(0);
cell60.CellStyle = style2;
cell60.SetCellValue(a11);
NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1);
cell61.CellStyle = style2;
cell61.SetCellValue(a11);
NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2);
cell62.CellStyle = style2;
cell62.SetCellValue(a22);
NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3);
cell63.CellStyle = style2;
cell63.SetCellValue(a33);
NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4);
cell64.CellStyle = style2;
cell64.SetCellValue(a44);
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("项目月测试次数", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public static bool OtchProtokolType2(Protokols_class.SGroup_class.Protokol_class Protokol, bool CreateNew = true, bool Open = true)
{
{
var MsgErr = "";
for (int i = 0; i < Protokol.MarkCount; i++)
{
var PAMIndex = -1;
for (int j = 0; j < Protokol.SampleCount; j++)
{
if (Protokol[j][i].LocalAlow && Protokol[j][i].Method.Length > 0)
{
if (PAMIndex < 0)
{
PAMIndex = j;
}
else if (Protokol[j][i].Method != Protokol[PAMIndex][i].Method)
{
MsgErr += '\n' + Protokol[j][i].Mark + " имеет различные методы у нормативов " + T.Object.Rows.Get <string>(Protokol[j].ObjectID, C.Object.Norm, C.Norm.Name) + " и " + T.Object.Rows.Get <string>(Protokol[PAMIndex].ObjectID, C.Object.Norm, C.Norm.Name);
}
}
}
}
if (MsgErr.Length > 0)
{
MessageBox.Show(MsgErr, "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
}
string NewFileName;
{
int Month, Year;
ATMisc.GetYearMonthFromYM(Employe_Form.SPoints.YM, out Year, out Month);
{
NewFileName = Application.StartupPath + "\\Отчеты\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
NewFileName += T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName) + "\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
NewFileName += ATMisc.GetMonthName1(Month) + "\\";
if (!Directory.Exists(NewFileName))
{
Directory.CreateDirectory(NewFileName);
}
}
NewFileName += ProtokolFileName(Protokol);
}
if (CreateNew || !File.Exists(NewFileName))
{
var WorkBook = ATMisc.GetGenericExcel("Протокол испытаний тип 2.xls");
if (WorkBook == null)
{
return(false);
}
var TitleSheet = WorkBook.GetSheet("Заголовок");
NPOI.SS.UserModel.ISheet Sheet1;
if (TitleSheet == null)
{
MessageBox.Show("В шаблоне не найден лист \"Заголовок\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
var DT = Protokol.Date;
GetProtokolsExchanges(TitleSheet
, ATMisc.GetDateTimeFromYM(Employe_Form.SPoints.YM).Year
, Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString()
, Protokol.Objects + ' ' + T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName)
, Protokol.Objects
, Protokol.ProtoType
, Protokol.ObjectsLocations
, Protokol.DateOstr
, Protokol.DateP
, Protokol.StrTime
, Protokol.Peoples
, Protokol.Causes
, Protokol.Numbers
, Protokol.Day.ToString()
, ATMisc.GetMonthName2(DT.Month)
, DT.Month.ToString()
, RCache.PSG.GetMethodName(Protokol.PodrID)
, T.PaPoS.Rows.Get <string>(Protokol.PaPoSID, C.PaPoS.Name)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.FllName)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName)
, T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.Contact)
, 0).Exchange();
{
var Exchanges = new CellExchange_Class(TitleSheet);
Exchanges.ClearExchanges();
Exchanges.AddColumn("{имя свойства}");
Exchanges.AddColumn("{ед. свойства}");
Exchanges.AddColumn("{значение свойства}");
if (Exchanges.CheckTableColumns())
{
for (int i = 0; i < Protokol[0].TCSCount; i++)
{
var Values = new object[3];
Values[0] = Protokol[0].TCSName(i);
Values[1] = Protokol[0].TCSEdType(i);
Values[2] = Protokol[0].TCSValue(i);
Exchanges.SetRow(Values);
}
}
}
if (Protokol.MarkCount == 0)
{
MessageBox.Show("Заполненые показатели не найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
string UnDeleteSheetName;
switch (Protokol.SGroup)
{
case data.SGroup.Group2:
UnDeleteSheetName = "Изначальный";
{
int Index = WorkBook.GetSheetIndex(UnDeleteSheetName);
Sheet1 = WorkBook.GetSheetAt(Index);
WorkBook.SetSheetName(Index, "Концентрации");
}
if (Sheet1 == null)
{
MessageBox.Show("В шаблоне не найден лист \"" + UnDeleteSheetName + "\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
var SPointIndex = new SColumn_struct(-1, null);
var OEdTypeIndex = new SColumn_struct(-1, null);
var OMethodIndex = new SColumn_struct(-1, null);
var OMarkNameIndex = new SColumn_struct(-1, null);
var NumberIndex = new SColumn_struct(-1, null);
var ResultIndex = new SColumn_struct(-1, null);
var ProbeIndex = new SColumn_struct(-1, null);
int RowIndex = -1;
var SPStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ
var PStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ
{
var OEdTypeRowIndex = -1;
var OMethodRowIndex = -1;
var OMarkNameRowIndex = -1;
var NumberRowIndex = -1;
var SPointRowIndex = -1;
var ResultRowIndex = -1;
var ProbeRowIndex = -1;
var ExistColumn = new CellExchange_Class(Sheet1);
ExistColumn.AddExchange("{номер п/п}", (Cell) =>
{
NumberRowIndex = Cell.RowIndex;
NumberIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{место отбора}", (Cell) =>
{
SPointRowIndex = Cell.RowIndex;
SPointIndex = new SColumn_struct(Cell);
SPStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх
CopyStyleFromCell(SPStyle[0], Cell); SPStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
SPStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина
CopyStyleFromCell(SPStyle[1], Cell); SPStyle[1].BorderBottom = SPStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
SPStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ
CopyStyleFromCell(SPStyle[2], Cell); SPStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
}, 5);
ExistColumn.AddExchange("{показатель}", (Cell) =>
{
OMarkNameRowIndex = Cell.RowIndex;
OMarkNameIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{проба}", (Cell) =>
{
ProbeRowIndex = Cell.RowIndex;
ProbeIndex = new SColumn_struct(Cell);
PStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх
CopyStyleFromCell(PStyle[0], Cell); PStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
PStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина
CopyStyleFromCell(PStyle[1], Cell); PStyle[1].BorderBottom = PStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
PStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ
CopyStyleFromCell(PStyle[2], Cell); PStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None;
}, 5);
ExistColumn.AddExchange("{ед.изм.}", (Cell) =>
{
OEdTypeRowIndex = Cell.RowIndex;
OEdTypeIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{методика}", (Cell) =>
{
OMethodRowIndex = Cell.RowIndex;
OMethodIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{результат}", (Cell) =>
{
ResultRowIndex = Cell.RowIndex;
ResultIndex = new SColumn_struct(Cell);
}, 5);
ExistColumn.AddExchange("{Дата}", DT.ToShortDateString(), 5);
ExistColumn.AddExchange("{Номер протокола}", Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString(), 5);
SetResp(ExistColumn, Protokol.PodrID, data.TResp.LaboratoryProtokol);
if (SPointRowIndex == -1 || OMarkNameRowIndex == -1 || OEdTypeRowIndex == -1 || OMethodRowIndex == -1 || ResultRowIndex == -1)
{
MessageBox.Show("Не все табличные метки найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
if (ProbeRowIndex > -1 && ProbeRowIndex != OMarkNameRowIndex || NumberRowIndex > -1 && NumberRowIndex != OMarkNameRowIndex || SPointRowIndex != OMarkNameRowIndex || OMarkNameRowIndex != OEdTypeRowIndex || OEdTypeRowIndex != OMethodRowIndex || OMethodRowIndex != ResultRowIndex)
{
MessageBox.Show("Все табличные метки должны распологаться в одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
RowIndex = SPointRowIndex;
}
Sheet1.ShiftRows(RowIndex, Sheet1.LastRowNum, Protokol.TableCount * Protokol.MarkCount - 1);
int ONumber = 0;
for (int i = 0; i < Protokol.TableCount; i++)
{
NPOI.SS.UserModel.IRow Row;
for (int j = 0; j < Protokol.MarkCount; j++)
{
Row = Sheet1.CreateRow(RowIndex++);
if (NumberIndex.Index > -1)
{
ATMisc.SetValue(Row, ++ONumber, NumberIndex.Index, NumberIndex.Style);
}
if (ProbeIndex.Index > -1)
{
ATMisc.SetValue(Row, Protokol[i].Number, ProbeIndex.Index, ProbeIndex.Style);
}
ATMisc.SetValue(Row, Protokol.GetMethod(j), OMethodIndex.Index, OMethodIndex.Style);
ATMisc.SetValue(Row, Protokol.GetMarkName(j), OMarkNameIndex.Index, OMarkNameIndex.Style);
ATMisc.SetValue(Row, Protokol.GetMarkEdType(j), OEdTypeIndex.Index, OEdTypeIndex.Style);
if (Protokol.IsSpetialOut(j, i))
{
ATMisc.SetValue(Row, Protokol.GetSpetialOut(j, i), ResultIndex.Index, ResultIndex.Style);
}
else
{
ATMisc.SetValue(Row, Protokol.GetMarkAmount(j, i), ResultIndex.Index, ResultIndex.Style);
}
}
Row = Sheet1.GetRow(RowIndex - Protokol.MarkCount);
switch (Protokol.MarkCount)
{
case 1:
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPointIndex.Style);
break;
case 2:
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index));
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]);
Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = SPStyle[2];
if (ProbeIndex.Index > -1)
{
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index));
}
break;
default:
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index));
ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]);
if (ProbeIndex.Index > -1)
{
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = PStyle[2];
Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index));
for (int s = Protokol.MarkCount - 2; s > 1; s--)
{
Sheet1.GetRow(RowIndex - 1 - s).GetCell(ProbeIndex.Index).CellStyle = PStyle[1];
//Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1]; //это тут лишнее
}
Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2];
Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2];
}
else
{
for (int s = Protokol.MarkCount - 2; s > 1; s--)
{
Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1];
}
Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2];
}
break;
}
}
break;
default: throw new Exception("Не верный тип протокола");
}
for (int i = 0; i < WorkBook.NumberOfSheets; i++)
{
if (WorkBook.GetSheetAt(i).SheetName.ToLower() != "заголовок" && WorkBook.GetSheetAt(i).SheetName.ToLower() != "концентрации")
{
WorkBook.RemoveSheetAt(i);
i--;
}
}
return(SaveExcel(WorkBook, NewFileName, Open));
}
else
{
if (Open)
{
System.Diagnostics.Process.Start(NewFileName);
}
return(true);
}
}
/// <summary>
/// 输出数据表格
/// </summary>
/// <param name="workbook">工作文档</param>
/// <param name="normalStyle">普通样式(用于表格内容)</param>
/// <param name="boldStyle">粗体样式(用于表格头部)</param>
/// <param name="table">表格数据</param>
public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table)
{
//创建Sheet页
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//行号
int rowIndex = 0;
//是否需要输出表头
bool isNeedCreateHeader = true;
//输出数据到Excel
foreach (DataRow rowData in table.Rows)
{
//忽略空数据行
if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count)
{
continue;
}
//列号
int colIndex = 0;
//Excel行
NPOI.SS.UserModel.IRow row = null;
//是否需要输入表头
if (isNeedCreateHeader)
{
isNeedCreateHeader = false;
//创建行
row = sheet.CreateRow(rowIndex);
//输出列名到Excel
colIndex = 0;
foreach (DataColumn kvp in table.Columns)
{
//列名
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = boldStyle;
//设置数据
cell.SetCellValue(kvp.ColumnName);
colIndex++;
}
rowIndex++;
}
//创建行
row = sheet.CreateRow(rowIndex);
//输出列值到Excel
colIndex = 0;
foreach (object val in rowData.ItemArray)
{
//列值
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = normalStyle;
//设置数据
//判断是否为空
if (val != null)
{
//不为空
//判断是否为RTF内容
if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name))
{
//RTF内容
cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val);
}
else
{
//文本内容
cell.SetCellValue(val.ToString());
}
}
else
{
//为空
cell.SetCellValue(string.Empty);
}
colIndex++;
}
rowIndex++;
}
//Excel列宽自动适应
if (table.Rows.Count >= 1 && sheet.GetRow(0) != null)
{
for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++)
{
sheet.AutoSizeColumn(k);
}
}
}
/// <summary>
/// 获取EXCEL数组
/// </summary>
/// <param name="dt"></param>
public static byte[] GetExcelFileByte(DataSet ds)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//设置样式
NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle();
NPOI.SS.UserModel.IFont font1 = book.CreateFont();
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font1.IsBold = true;
style1.SetFont(font1);
NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
byte[] fileByte = null;
for (int k = 0; k < ds.Tables.Count; k++)
{
try
{
DataTable dt = ds.Tables[k];
if (null != dt && dt.Rows.Count > 0)
{
if (dt.TableName == null || dt.TableName == "")
{
dt.TableName = "Sheet1";
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
sheet.DefaultColumnWidth = 20;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row.Cells[i].CellStyle = style1;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
row2.Cells[j].CellStyle = style2;
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
try
{
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
fileByte = ms.GetBuffer();
//using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
}
}
catch (Exception)
{
throw;
}
finally
{
if (null != book)
{
book.Close();
book = null;
}
}
return(fileByte);
}
public static bool OtchMassOutgo(uint PodrID, int YMFrom, Podrs_class.PeriodType pt, uint OTypeID, bool ShowErrorMessage = true, bool CreateNew = true, bool Open = true)
{
bool Returning = false;
RCache.Podrs = new RCache.Podrs_class();
var Podrs = new Podrs_class(PodrID, pt, YMFrom, OTypeID);
int Year, Month;
ATMisc.GetYearMonthFromYM(YMFrom, out Year, out Month);
if (Podrs.MarksCount == 0)
{
if (ShowErrorMessage)
{
MessageBox.Show("Показатели не обнаружены", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
{
int tMCount = 0;
for (int i = 0; i < Podrs.MarkCount; i++)
{
if (Podrs.ShowMark(i))
{
tMCount++;
}
}
if (tMCount == 0)
{
if (ShowErrorMessage)
{
MessageBox.Show("Показатели не обнаружены", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
}
G.OPType.QUERRY().SHOW.DO();
var StylesEDType = new NPOI.SS.UserModel.ICellStyle[G.OPType.Rows.Count];
if (!Directory.Exists(Application.StartupPath + "\\Отчеты"))
{
Directory.CreateDirectory(Application.StartupPath + "\\Отчеты");
}
for (int i = 0; i < Podrs.Count; i++) //подразделения
{
if (Podrs[i].MarksCount > 0)
{
var NewFileName = Application.StartupPath + "\\Отчеты\\Расчет массы " + Podrs.PeriodName() + ' ' + Year.ToString() + ' ' + T.Podr.Rows.Get <string>(Podrs[i].PodrID, C.Podr.ShrName) + ".xls";
if (CreateNew || !File.Exists(NewFileName))
{
var WorkBook = ATMisc.GetGenericExcel("Расчет массы.xls");
if (WorkBook == null)
{
return(false);
}
var Sheet1 = WorkBook.GetSheet("Таблица");
if (Sheet1 == null)
{
if (ShowErrorMessage)
{
MessageBox.Show("В шаблоне не найден лист \"Заголовок\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
var Exchanges = new CellExchange_Class(Sheet1);
NPOI.SS.UserModel.ICellStyle NumberStyle = null
, NameStyle = null
, EdTypeStyle = null
, CodeStyle = null
, FactStyle = null
, BackStyle = null
, CalcStyle = null
, MassStyle = null;
var Podr = RCache.Podrs[Podrs[i].PodrID];
Exchanges.AddExchange("{сооружение}", Podr.ShortName, 2);
for (int s = 0; s < Podrs[i].Count; s++)
{
if (Podrs[i][s].ObjectID > 0)
{
Exchanges.AddExchange("{объект}", T.Object.Rows.Get <string>(Podrs[i][s].ObjectID, C.Object.OType, C.OType.Name).ToLower(), 2);
break;
}
}
if (Podr.Ppls.Count == 0 || (data.PnMean)Podr.Ppls[0].PnMeanID != data.PnMean.Nachalnic)
{
if (ShowErrorMessage)
{
MessageBox.Show("У сооружения " + Podr.ShortName + " нет начальника.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
Exchanges.AddExchange("{начальник сооружения}", Podr.Ppls[0].PeopleName, 2);
Exchanges.AddExchange("{профессия начальника}", Podr.Ppls[0].Profession, 2);
{
var PeopleID = RCache.PSG.GetPeopleID(Podr.PSG);
Exchanges.AddExchange("{профессия ответственного}", T.People.Rows.Get <string>(PeopleID, C.People.Prfssn, C.Prfssn.Name), 2);
Exchanges.AddExchange("{ФИО ответственного}", Misc.GetShortFIO(PeopleID), 2);
}
Exchanges.AddExchange("{год}", Year.ToString(), 5);
int NumberIndex = -1, NameIndex = -1, EdTypeIndex = -1, CodeIndex = -1, FactIndex = -1, BackIndex = -1, CalcIndex = -1, MassIndex = -1, RowIndex = -1;
{
int NumberRowIndex = -1, NameRowIndex = -1, EdTypeRowIndex = -1, CodeRowIndex = -1, FactRowIndex = -1, BackRowIndex = -1, CalcRowIndex = -1, MassRowIndex = -1;
Exchanges.AddExchange("{выпуск}", Cell =>
{
NumberIndex = Cell.ColumnIndex;
NumberRowIndex = Cell.RowIndex;
NumberStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{имя}",
Cell =>
{
NameIndex = Cell.ColumnIndex;
NameRowIndex = Cell.RowIndex;
NameStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{едизм}",
Cell =>
{
EdTypeIndex = Cell.ColumnIndex;
EdTypeRowIndex = Cell.RowIndex;
EdTypeStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{код}",
Cell =>
{
CodeIndex = Cell.ColumnIndex;
CodeRowIndex = Cell.RowIndex;
CodeStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{факт}",
Cell =>
{
FactIndex = Cell.ColumnIndex;
FactRowIndex = Cell.RowIndex;
FactStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{фон}",
Cell =>
{
BackIndex = Cell.ColumnIndex;
BackRowIndex = Cell.RowIndex;
BackStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{расчет}",
Cell =>
{
CalcIndex = Cell.ColumnIndex;
CalcRowIndex = Cell.RowIndex;
CalcStyle = Cell.CellStyle;
}, 1);
Exchanges.AddExchange("{масса}",
Cell =>
{
MassIndex = Cell.ColumnIndex;
MassRowIndex = Cell.RowIndex;
MassStyle = Cell.CellStyle;
for (int j = 0; j < StylesEDType.Length; j++)
{
StylesEDType[j] = WorkBook.CreateCellStyle();
CopyStyleFromCell(StylesEDType[j], Cell);
StylesEDType[j].DataFormat = WorkBook.CreateDataFormat().GetFormat("#,##0.000\" " + G.OPType.Rows.Get <string>(j, C.OPType.EdTypeT, C.EdType.Name) + "\"");
}
}, 1);
Exchanges.AddExchange("{период}", Podrs.PeriodName(), 2);
SetResp(Exchanges, Podr.ID, data.TResp.LaboratoryProtokol);
if (NameRowIndex == NumberRowIndex && NumberRowIndex == FactRowIndex && NumberRowIndex == BackRowIndex && BackRowIndex == CalcRowIndex && NumberRowIndex == MassRowIndex)
{
if (MassRowIndex == -1)
{
if (ShowErrorMessage)
{
MessageBox.Show("Критически важные колонки таблицы не найдены(выпуск,наименование, факт, фон, расчет, масса).", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
}
else
{
if (ShowErrorMessage)
{
MessageBox.Show("Метки всех колонок должны находиться на одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
RowIndex = NumberRowIndex;
Sheet1.ShiftRows(RowIndex + 1, Sheet1.LastRowNum, -1);
}
Sheet1.SetColumnHidden(BackIndex, !Podrs.AllowBack);
Sheet1.SetColumnHidden(CalcIndex, !Podrs.AllowBack);
if (!Podrs.AllowBack)
{
int NewWidth = Sheet1.GetColumnWidth(BackIndex) + Sheet1.GetColumnWidth(FactIndex) + Sheet1.GetColumnWidth(CalcIndex);
Sheet1.SetColumnWidth(FactIndex, NewWidth);
}
for (int j = 0; j < Podrs[i].Count; j++) //выпуск
{
Sheet1.ShiftRows(RowIndex, Sheet1.LastRowNum, Podrs[i][j].Count + 2);
var LastRow = Sheet1.CreateRow(RowIndex + Podrs[i][j].Count);
if (EdTypeIndex > -1)
{
ATMisc.SetValue(LastRow, "тыс.м3", EdTypeIndex, EdTypeStyle);
}
ATMisc.SetValue(LastRow, T.Object.Rows.Get <string>(Podrs[i][j].ObjectID, C.Object.OLocationTo, C.OLocation.Name), MassIndex, MassStyle);
if (Podrs[i][j].Count > 0)
{
for (int k = 0; k < Podrs[i][j].Count; k++)
{
var MarkID = Podrs[i][j].MarkID(k);
var Row = Sheet1.CreateRow(RowIndex++);
if (NameIndex > -1)
{
ATMisc.SetValue(Row, T.Mark.Rows.Get <string>(MarkID, C.Mark.Name), NameIndex, NameStyle);
}
if (EdTypeIndex > -1)
{
ATMisc.SetValue(Row, T.Mark.Rows.Get <string>(MarkID, C.Mark.EdType, C.EdType.Name), EdTypeIndex, EdTypeStyle);
}
if (CodeIndex > -1)
{
ATMisc.SetValue(Row, T.Mark.Rows.Get <int>(MarkID, C.Mark.Code), CodeIndex, CodeStyle);
}
double Front = RCache.Marks[MarkID].GetRound(Podrs[i][j][k].FMiddle)
, Back = RCache.Marks[MarkID].GetRound(Podrs[i][j][k].BMiddle);
ATMisc.SetValue(Row, Front, FactIndex, FactStyle);
ATMisc.SetValue(Row, Back, BackIndex, BackStyle);
ATMisc.SetFormula(Row, "if(" + GetColCharName(FactIndex) + RowIndex.ToString() + "<=" + GetColCharName(BackIndex) + RowIndex.ToString() + ", \"<=фон\", " + GetColCharName(FactIndex) + RowIndex.ToString() + "-" + GetColCharName(BackIndex) + RowIndex.ToString() + ")", CalcIndex, CalcStyle);
ATMisc.SetFormula(Row, "if(" + GetColCharName(CalcIndex) + RowIndex.ToString() + "=\"<=фон\",\"\", " + GetColCharName(CalcIndex) + RowIndex.ToString() + "*" + GetColCharName(FactIndex) + (LastRow.RowNum + 1).ToString() + "*" + DTSF(RCache.Marks[MarkID].Multyply) + ")", MassIndex, StylesEDType[G.OPType.Rows.GetIndex(RCache.Marks[MarkID].OPTypeID)]);
if (NumberIndex > -1)
{
ATMisc.SetValue(Row, "", NumberIndex, NumberStyle);
}
}
if (NumberIndex > -1)
{
ATMisc.SetValue(Sheet1.GetRow(RowIndex - Podrs[i][j].Count), Podrs[i][j].VShortName + " [" + (j + 1).ToString() + "]", NumberIndex, NumberStyle);
}
if (NumberIndex > -1)
{
ATMisc.SetValue(LastRow, "", NumberIndex, NumberStyle);
}
}
else
{
if (NumberIndex > -1)
{
ATMisc.SetValue(LastRow, Podrs[i][j].VShortName + " [" + (j + 1).ToString() + "]", NumberIndex, NumberStyle);
}
}
RowIndex++;
ATMisc.SetValue(LastRow, Podrs[i][j].VolumeSumm, FactIndex, FactStyle);
ATMisc.SetValue(LastRow, "Объём сброшеных вод", NameIndex, NameStyle);
if (EdTypeIndex > -1)
{
ATMisc.SetValue(LastRow, "тыс.м3", EdTypeIndex, EdTypeStyle);
}
if (CodeIndex > -1)
{
ATMisc.SetValue(LastRow, "", CodeIndex, CodeStyle);
}
ATMisc.SetValue(LastRow, "", BackIndex, BackStyle);
ATMisc.SetValue(LastRow, "", CalcIndex, CalcStyle);
Sheet1.CreateRow(RowIndex++).Height = 50;
}
Returning = Returning || SaveExcel(WorkBook, NewFileName, Open);
}
else if (Open && File.Exists(NewFileName))
{
System.Diagnostics.Process.Start(NewFileName);
Returning = true;
}
}
}
return(Returning);
}
public static void CreateCell(NPOI.SS.UserModel.IRow row, int index, object value, NPOI.SS.UserModel.ICellStyle cellStyle)
{
var cell = row.CreateCell(index);
if (value is decimal)
{
cell.SetCellValue((double)((decimal)value));
}
else
if (value is int)
{
cell.SetCellValue((int)value);
}
else
{
cell.SetCellValue((string)value);
}
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
protected void lbtn_export_ribao_export_Click(object sender, EventArgs e)
{
string url = datemin.Value;
if (url == "")
{
SystemTool.AlertShow(this, "开始日期不能为空");
return;
}
url = datemax.Value;
if (url == "")
{
SystemTool.AlertShow(this, "结束日期不能为空");
return;
}
url = img1.ImageUrl;
if (url == "")
{
SystemTool.AlertShow(this, "图片为空，请先刷新");
return;
}
if (ViewState["date"].ToString() != datemin.Value || ViewState["date2"].ToString() != datemax.Value)
{
SystemTool.AlertShow(this, "当前图片与日期不一致，请先刷新后再导出此报表");
return;
}
string imgurl = Server.MapPath("~/" + url);
//Response.Write("<script>window.open('print.aspx?date="+datemin.Value+"&url="+url+"','_blank')</script>");
string sql = @"select facename,sensorNo,bracketNo,distance,max(zlmax) zlmax,AVG(zlavg) zlavg,min(zlmin) zlmin,MAX(cclmax) cclmax,AVG(cclavg) cclavg,MAX(mzlmax) mzlmax,AVG(mzlavg) mzlavg from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' group by bracketNo,sensorNo,distance,facename order by bracketno";
DataSet ds = DB.ExecuteSqlDataSet(sql, null);
if (ds.Tables[0].Rows.Count <= 0)
{
SystemTool.AlertShow(this, "报表为空，请在初撑力与末阻力页面查询数据后再导出此报表");
return;
}
//整面最大值、最小值
string zhengmianmax = ds.Tables[0].Compute("Max(zlmax)", "true").ToString();
string zhengmianmin = ds.Tables[0].Compute("Min(zlmin)", "true").ToString();
string zhengmianavg = Convert.ToDecimal(ds.Tables[0].Compute("avg(zlavg)", "true").ToString()).ToString("0.00");
string shangbumax = "0.00";
string shangbumin = "0.00";
string shangbuavg = "0.00";
string zhongbumax = "0.00";
string zhongbumin = "0.00";
string zhongbuavg = "0.00";
string xiabumax = "0.00";
string xiabumin = "0.00";
string xiabuavg = "0.00";
string sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='上部'";
DataSet dsszx = DB.ExecuteSqlDataSet(sqlszx, null);
if (dsszx.Tables[0].Rows.Count > 0)
{
shangbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
shangbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
shangbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
}
sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='中部'";
dsszx = DB.ExecuteSqlDataSet(sqlszx, null);
if (dsszx.Tables[0].Rows.Count > 0)
{
zhongbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
zhongbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
zhongbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
}
sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='下部'";
dsszx = DB.ExecuteSqlDataSet(sqlszx, null);
if (dsszx.Tables[0].Rows.Count > 0)
{
xiabumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
xiabumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
xiabuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.ICellStyle styleleft = book.CreateCellStyle();
//设置单元格的样式：水平对齐居左
styleleft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
styleleft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleleft.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleleft.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleleft.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.ICellStyle styleleftcenter = book.CreateCellStyle();
//设置单元格的样式：居左居中
styleleftcenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
styleleftcenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
styleleftcenter.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleleftcenter.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleleftcenter.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleleftcenter.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleleftcenter.WrapText = true;
//将新的样式赋给单元格
//cell.CellStyle = style;
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
//第一行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("综采支架压力综合日报表【报表日期" + datemin.Value + "-" + datemax.Value + "】");
cell0.CellStyle = style;
row.CreateCell(1).CellStyle = style; row.CreateCell(2).CellStyle = style; row.CreateCell(3).CellStyle = style; row.CreateCell(4).CellStyle = style; row.CreateCell(5).CellStyle = style; row.CreateCell(6).CellStyle = style; row.CreateCell(7).CellStyle = style; row.CreateCell(8).CellStyle = style; row.CreateCell(9).CellStyle = style;
//第二行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1);
NPOI.SS.UserModel.ICell cell1 = row1.CreateCell(0);
cell1.SetCellValue("单位：兆帕 工作面名称：" + ds.Tables[0].Rows[0]["facename"].ToString() + " 认证编号： 打印日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
cell1.CellStyle = style;
row1.CreateCell(1).CellStyle = style; row1.CreateCell(2).CellStyle = style; row1.CreateCell(3).CellStyle = style; row1.CreateCell(4).CellStyle = style; row1.CreateCell(5).CellStyle = style; row1.CreateCell(6).CellStyle = style; row1.CreateCell(7).CellStyle = style; row1.CreateCell(8).CellStyle = style; row1.CreateCell(9).CellStyle = style;
// 第三行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(2);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(0);
cell2.CellStyle = style;
row2.CreateCell(1).CellStyle = style; row2.CreateCell(2).CellStyle = style; row2.CreateCell(3).CellStyle = style; row2.CreateCell(4).CellStyle = style; row2.CreateCell(5).CellStyle = style; row2.CreateCell(6).CellStyle = style; row2.CreateCell(7).CellStyle = style; row2.CreateCell(8).CellStyle = style; row2.CreateCell(9).CellStyle = style;
row2.Height = 4800;
//将图片文件读入一个字符串
byte[] bytes = System.IO.File.ReadAllBytes(imgurl);
int pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
//dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
//dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//col1和row1 :图片左上角的位置，以excel单元格为参考,比喻这两个值为(1,1)，那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
//col2和row2:图片右下角的位置，以excel单元格为参考,比喻这两个值为(2,2)，那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
NPOI.HSSF.UserModel.HSSFClientAnchor anchor = new NPOI.HSSF.UserModel.HSSFClientAnchor(10, 10, 10, 10, 0, 2, 9, 3);
//把图片插到相应的位置
NPOI.HSSF.UserModel.HSSFPicture pict = (NPOI.HSSF.UserModel.HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//第三行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 9));
NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(3);
NPOI.SS.UserModel.ICell cell3 = row3.CreateCell(0);
cell3.SetCellValue("监测数据统计表：");
cell3.CellStyle = styleleft;
row3.CreateCell(1).CellStyle = styleleft; row3.CreateCell(2).CellStyle = styleleft; row3.CreateCell(3).CellStyle = styleleft; row3.CreateCell(4).CellStyle = styleleft; row3.CreateCell(5).CellStyle = styleleft; row3.CreateCell(6).CellStyle = styleleft; row3.CreateCell(7).CellStyle = styleleft; row3.CreateCell(8).CellStyle = styleleft; row3.CreateCell(9).CellStyle = styleleft;
//第四行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 8, 0, 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 2, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 5));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 8, 9));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 2, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 4, 5));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 8, 9));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 2, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 4, 5));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 8, 9));
NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(4);
NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0);
cell40.SetCellValue("工作面工作阻力统计：");
cell40.CellStyle = style;
NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style;
NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.SetCellValue("整面"); cell42.CellStyle = style;
NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style;
NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.SetCellValue("上部"); cell44.CellStyle = style;
NPOI.SS.UserModel.ICell cell45 = row4.CreateCell(5); cell45.CellStyle = style;
NPOI.SS.UserModel.ICell cell46 = row4.CreateCell(6); cell46.SetCellValue("中部"); cell46.CellStyle = style;
NPOI.SS.UserModel.ICell cell47 = row4.CreateCell(7); cell47.CellStyle = style;
NPOI.SS.UserModel.ICell cell48 = row4.CreateCell(8); cell48.SetCellValue("下部"); cell48.CellStyle = style;
NPOI.SS.UserModel.ICell cell49 = row4.CreateCell(9); cell49.CellStyle = style;
NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(5);
NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style;
NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style;
NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.SetCellValue("最大"); cell52.CellStyle = style;
NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.SetCellValue("最小"); cell53.CellStyle = style;
NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.SetCellValue("最大"); cell54.CellStyle = style;
NPOI.SS.UserModel.ICell cell55 = row5.CreateCell(5); cell55.SetCellValue("最小"); cell55.CellStyle = style;
NPOI.SS.UserModel.ICell cell56 = row5.CreateCell(6); cell56.SetCellValue("最大"); cell56.CellStyle = style;
NPOI.SS.UserModel.ICell cell57 = row5.CreateCell(7); cell57.SetCellValue("最小"); cell57.CellStyle = style;
NPOI.SS.UserModel.ICell cell58 = row5.CreateCell(8); cell58.SetCellValue("最大"); cell58.CellStyle = style;
NPOI.SS.UserModel.ICell cell59 = row5.CreateCell(9); cell59.SetCellValue("最小"); cell59.CellStyle = style;
NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(6);
NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style;
NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.SetCellValue("平均"); cell62.CellStyle = style;
NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style;
NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.SetCellValue("平均"); cell64.CellStyle = style;
NPOI.SS.UserModel.ICell cell65 = row6.CreateCell(5); cell65.CellStyle = style;
NPOI.SS.UserModel.ICell cell66 = row6.CreateCell(6); cell66.SetCellValue("平均"); cell66.CellStyle = style;
NPOI.SS.UserModel.ICell cell67 = row6.CreateCell(7); cell67.CellStyle = style;
NPOI.SS.UserModel.ICell cell68 = row6.CreateCell(8); cell68.SetCellValue("平均"); cell68.CellStyle = style;
NPOI.SS.UserModel.ICell cell69 = row6.CreateCell(9); cell69.CellStyle = style;
NPOI.SS.UserModel.IRow row7 = sheet.CreateRow(7);
NPOI.SS.UserModel.ICell cell70 = row7.CreateCell(0); cell70.CellStyle = style;
NPOI.SS.UserModel.ICell cell71 = row7.CreateCell(1); cell71.CellStyle = style;
NPOI.SS.UserModel.ICell cell72 = row7.CreateCell(2); cell72.SetCellValue(zhengmianmax); cell72.CellStyle = style;
NPOI.SS.UserModel.ICell cell73 = row7.CreateCell(3); cell73.SetCellValue(zhengmianmin); cell73.CellStyle = style;
NPOI.SS.UserModel.ICell cell74 = row7.CreateCell(4); cell74.SetCellValue(shangbumax); cell74.CellStyle = style;
NPOI.SS.UserModel.ICell cell75 = row7.CreateCell(5); cell75.SetCellValue(shangbumin); cell75.CellStyle = style;
NPOI.SS.UserModel.ICell cell76 = row7.CreateCell(6); cell76.SetCellValue(zhongbumax); cell76.CellStyle = style;
NPOI.SS.UserModel.ICell cell77 = row7.CreateCell(7); cell77.SetCellValue(zhongbumin); cell77.CellStyle = style;
NPOI.SS.UserModel.ICell cell78 = row7.CreateCell(8); cell78.SetCellValue(xiabumax); cell78.CellStyle = style;
NPOI.SS.UserModel.ICell cell79 = row7.CreateCell(9); cell79.SetCellValue(xiabumin); cell79.CellStyle = style;
NPOI.SS.UserModel.IRow row8 = sheet.CreateRow(8);
NPOI.SS.UserModel.ICell cell80 = row8.CreateCell(0); cell80.CellStyle = style;
NPOI.SS.UserModel.ICell cell81 = row8.CreateCell(1); cell81.CellStyle = style;
NPOI.SS.UserModel.ICell cell82 = row8.CreateCell(2); cell82.SetCellValue(zhengmianavg); cell82.CellStyle = style;
NPOI.SS.UserModel.ICell cell83 = row8.CreateCell(3); cell83.CellStyle = style;
NPOI.SS.UserModel.ICell cell84 = row8.CreateCell(4); cell84.SetCellValue(shangbuavg); cell84.CellStyle = style;
NPOI.SS.UserModel.ICell cell85 = row8.CreateCell(5); cell85.CellStyle = style;
NPOI.SS.UserModel.ICell cell86 = row8.CreateCell(6); cell86.SetCellValue(zhongbuavg); cell86.CellStyle = style;
NPOI.SS.UserModel.ICell cell87 = row8.CreateCell(7); cell87.CellStyle = style;
NPOI.SS.UserModel.ICell cell88 = row8.CreateCell(8); cell88.SetCellValue(xiabuavg); cell88.CellStyle = style;
NPOI.SS.UserModel.ICell cell89 = row8.CreateCell(9); cell89.CellStyle = style;
//第5行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 0, 0));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 1, 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 2, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 4, 5));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 6, 7));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 8, 9));
NPOI.SS.UserModel.IRow row9 = sheet.CreateRow(9);
NPOI.SS.UserModel.ICell cell90 = row9.CreateCell(0); cell90.SetCellValue("分机编号"); cell90.CellStyle = style;
NPOI.SS.UserModel.ICell cell91 = row9.CreateCell(1); cell91.SetCellValue("支架编号"); cell91.CellStyle = style;
NPOI.SS.UserModel.ICell cell92 = row9.CreateCell(2); cell92.SetCellValue("安装位置"); cell92.CellStyle = style;
NPOI.SS.UserModel.ICell cell93 = row9.CreateCell(3); cell93.CellStyle = style;
NPOI.SS.UserModel.ICell cell94 = row9.CreateCell(4); cell94.SetCellValue("工作阻力"); cell94.CellStyle = style;
NPOI.SS.UserModel.ICell cell95 = row9.CreateCell(5); cell95.CellStyle = style;
NPOI.SS.UserModel.ICell cell96 = row9.CreateCell(6); cell96.SetCellValue("初撑力"); cell96.CellStyle = style;
NPOI.SS.UserModel.ICell cell97 = row9.CreateCell(7); cell97.CellStyle = style;
NPOI.SS.UserModel.ICell cell98 = row9.CreateCell(8); cell98.SetCellValue("末阻力"); cell98.CellStyle = style;
NPOI.SS.UserModel.ICell cell99 = row9.CreateCell(9); cell99.CellStyle = style;
NPOI.SS.UserModel.IRow row10 = sheet.CreateRow(10);
NPOI.SS.UserModel.ICell cell100 = row10.CreateCell(0); cell100.CellStyle = style;
NPOI.SS.UserModel.ICell cell101 = row10.CreateCell(1); cell101.CellStyle = style;
NPOI.SS.UserModel.ICell cell102 = row10.CreateCell(2); cell102.CellStyle = style;
NPOI.SS.UserModel.ICell cell103 = row10.CreateCell(3); cell103.CellStyle = style;
NPOI.SS.UserModel.ICell cell104 = row10.CreateCell(4); cell104.SetCellValue("最大"); cell104.CellStyle = style;
NPOI.SS.UserModel.ICell cell105 = row10.CreateCell(5); cell105.SetCellValue("平均"); cell105.CellStyle = style;
NPOI.SS.UserModel.ICell cell106 = row10.CreateCell(6); cell106.SetCellValue("最大"); cell106.CellStyle = style;
NPOI.SS.UserModel.ICell cell107 = row10.CreateCell(7); cell107.SetCellValue("平均"); cell107.CellStyle = style;
NPOI.SS.UserModel.ICell cell108 = row10.CreateCell(8); cell108.SetCellValue("最大"); cell108.CellStyle = style;
NPOI.SS.UserModel.ICell cell109 = row10.CreateCell(9); cell109.SetCellValue("平均"); cell109.CellStyle = style;
//
string jiancefenxi = "支架编号：";
for (int i = 11; i < ds.Tables[0].Rows.Count + 11; i++)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, 2, 3));
NPOI.SS.UserModel.IRow row11 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell110 = row11.CreateCell(0); cell110.SetCellValue(ds.Tables[0].Rows[i - 11]["sensorNo"].ToString()); cell110.CellStyle = style;
NPOI.SS.UserModel.ICell cell111 = row11.CreateCell(1); cell111.SetCellValue(ds.Tables[0].Rows[i - 11]["bracketno"].ToString()); cell111.CellStyle = style;
NPOI.SS.UserModel.ICell cell112 = row11.CreateCell(2); cell112.SetCellValue(ds.Tables[0].Rows[i - 11]["distance"].ToString()); cell112.CellStyle = style;
NPOI.SS.UserModel.ICell cell113 = row11.CreateCell(3); cell113.CellStyle = style;
NPOI.SS.UserModel.ICell cell114 = row11.CreateCell(4); cell114.SetCellValue(ds.Tables[0].Rows[i - 11]["zlmax"].ToString()); cell114.CellStyle = style;
NPOI.SS.UserModel.ICell cell115 = row11.CreateCell(5); cell115.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["zlavg"].ToString()).ToString("0.00")); cell115.CellStyle = style;
NPOI.SS.UserModel.ICell cell116 = row11.CreateCell(6); cell116.SetCellValue(ds.Tables[0].Rows[i - 11]["cclmax"].ToString()); cell116.CellStyle = style;
NPOI.SS.UserModel.ICell cell117 = row11.CreateCell(7); cell117.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["cclavg"].ToString()).ToString("0.00")); cell117.CellStyle = style;
NPOI.SS.UserModel.ICell cell118 = row11.CreateCell(8); cell118.SetCellValue(ds.Tables[0].Rows[i - 11]["mzlmax"].ToString()); cell118.CellStyle = style;
NPOI.SS.UserModel.ICell cell119 = row11.CreateCell(9); cell119.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["mzlavg"].ToString()).ToString("0.00")); cell119.CellStyle = style;
if (ViewState["yujingzhi"].ToString() != "0")
{
decimal fenxi = Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == "" ? "0.00" : ds.Tables[0].Rows[i - 11]["zlavg"].ToString());
decimal yujingzhi = Convert.ToDecimal(ViewState["yujingzhi"].ToString());
if (fenxi >= yujingzhi)
{
jiancefenxi += "" + ds.Tables[0].Rows[i - 11]["bracketno"].ToString() + "超压（" + fenxi.ToString("0.00") + "），";
}
}
}
jiancefenxi += "请注意观察";
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 11, ds.Tables[0].Rows.Count + 11, 0, 9));
NPOI.SS.UserModel.IRow rowa = sheet.CreateRow(ds.Tables[0].Rows.Count + 11);
NPOI.SS.UserModel.ICell cella0 = rowa.CreateCell(0); cella0.SetCellValue("监测分析结论："); cella0.CellStyle = styleleft;
NPOI.SS.UserModel.ICell cella1 = rowa.CreateCell(1); cella1.CellStyle = style;
NPOI.SS.UserModel.ICell cella2 = rowa.CreateCell(2); cella2.CellStyle = style;
NPOI.SS.UserModel.ICell cella3 = rowa.CreateCell(3); cella3.CellStyle = style;
NPOI.SS.UserModel.ICell cella4 = rowa.CreateCell(4); cella4.CellStyle = style;
NPOI.SS.UserModel.ICell cella5 = rowa.CreateCell(5); cella5.CellStyle = style;
NPOI.SS.UserModel.ICell cella6 = rowa.CreateCell(6); cella6.CellStyle = style;
NPOI.SS.UserModel.ICell cella7 = rowa.CreateCell(7); cella7.CellStyle = style;
NPOI.SS.UserModel.ICell cella8 = rowa.CreateCell(8); cella8.CellStyle = style;
NPOI.SS.UserModel.ICell cella9 = rowa.CreateCell(9); cella9.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 12, ds.Tables[0].Rows.Count + 12, 0, 9));
NPOI.SS.UserModel.IRow rowb = sheet.CreateRow(ds.Tables[0].Rows.Count + 12);
rowb.Height = 2400;
NPOI.SS.UserModel.ICell cellb0 = rowb.CreateCell(0); cellb0.SetCellValue("" + jiancefenxi); cellb0.CellStyle = styleleftcenter;
NPOI.SS.UserModel.ICell cellb1 = rowb.CreateCell(1); cellb1.CellStyle = style;
NPOI.SS.UserModel.ICell cellb2 = rowb.CreateCell(2); cellb2.CellStyle = style;
NPOI.SS.UserModel.ICell cellb3 = rowb.CreateCell(3); cellb3.CellStyle = style;
NPOI.SS.UserModel.ICell cellb4 = rowb.CreateCell(4); cellb4.CellStyle = style;
NPOI.SS.UserModel.ICell cellb5 = rowb.CreateCell(5); cellb5.CellStyle = style;
NPOI.SS.UserModel.ICell cellb6 = rowb.CreateCell(6); cellb6.CellStyle = style;
NPOI.SS.UserModel.ICell cellb7 = rowb.CreateCell(7); cellb7.CellStyle = style;
NPOI.SS.UserModel.ICell cellb8 = rowb.CreateCell(8); cellb8.CellStyle = style;
NPOI.SS.UserModel.ICell cellb9 = rowb.CreateCell(9); cellb9.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 13, ds.Tables[0].Rows.Count + 13, 0, 9));
NPOI.SS.UserModel.IRow rowc = sheet.CreateRow(ds.Tables[0].Rows.Count + 13);
NPOI.SS.UserModel.ICell cellc0 = rowc.CreateCell(0); cellc0.SetCellValue("区队意见："); cellc0.CellStyle = styleleft;
NPOI.SS.UserModel.ICell cellc1 = rowc.CreateCell(1); cellc1.CellStyle = style;
NPOI.SS.UserModel.ICell cellc2 = rowc.CreateCell(2); cellc2.CellStyle = style;
NPOI.SS.UserModel.ICell cellc3 = rowc.CreateCell(3); cellc3.CellStyle = style;
NPOI.SS.UserModel.ICell cellc4 = rowc.CreateCell(4); cellc4.CellStyle = style;
NPOI.SS.UserModel.ICell cellc5 = rowc.CreateCell(5); cellc5.CellStyle = style;
NPOI.SS.UserModel.ICell cellc6 = rowc.CreateCell(6); cellc6.CellStyle = style;
NPOI.SS.UserModel.ICell cellc7 = rowc.CreateCell(7); cellc7.CellStyle = style;
NPOI.SS.UserModel.ICell cellc8 = rowc.CreateCell(8); cellc8.CellStyle = style;
NPOI.SS.UserModel.ICell cellc9 = rowc.CreateCell(9); cellc9.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 14, ds.Tables[0].Rows.Count + 14, 0, 9));
NPOI.SS.UserModel.IRow rowd = sheet.CreateRow(ds.Tables[0].Rows.Count + 14);
rowd.Height = 2400;
NPOI.SS.UserModel.ICell celld0 = rowd.CreateCell(0); celld0.SetCellValue(" 领导签字：____________部门签字：____________报表人：____________"); celld0.CellStyle = styleleft;
NPOI.SS.UserModel.ICell celld1 = rowd.CreateCell(1); celld1.CellStyle = style;
NPOI.SS.UserModel.ICell celld2 = rowd.CreateCell(2); celld2.CellStyle = style;
NPOI.SS.UserModel.ICell celld3 = rowd.CreateCell(3); celld3.CellStyle = style;
NPOI.SS.UserModel.ICell celld4 = rowd.CreateCell(4); celld4.CellStyle = style;
NPOI.SS.UserModel.ICell celld5 = rowd.CreateCell(5); celld5.CellStyle = style;
NPOI.SS.UserModel.ICell celld6 = rowd.CreateCell(6); celld6.CellStyle = style;
NPOI.SS.UserModel.ICell celld7 = rowd.CreateCell(7); celld7.CellStyle = style;
NPOI.SS.UserModel.ICell celld8 = rowd.CreateCell(8); celld8.CellStyle = style;
NPOI.SS.UserModel.ICell celld9 = rowd.CreateCell(9); celld9.CellStyle = style;
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy)
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Plan 1");
int rowNumer = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumer);
NPOI.SS.UserModel.ICell cell;
NPOI.SS.UserModel.IFont hFont = workbook.CreateFont();
hFont.FontHeightInPoints = 12;
hFont.FontName = "Arial";
NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle();
styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleHeader.SetFont(hFont);
NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle();
styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleDisabled.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleDisabled.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var listColumns = new List <ExportColumnInfo>();
listColumns.Add(new ExportColumnInfo()
{
Name = groupBy.GetDescription()
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Quantidade"
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Preço Médio"
});
for (int i = 0; i < listColumns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(listColumns[i].Name);
cell.CellStyle = styleHeader;
}
//---- row
foreach (var item in list)
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType);
row.CreateCell(1).SetCellValue(item.Quantity);
row.CreateCell(2).SetCellValue((double)item.AveragePrice);
}
for (int i = 0; i < listColumns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
byte[] byteArray;
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
workbook.Write(stream);
byteArray = stream.ToArray();
}
return(byteArray);
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
if (sfdReporters.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
//输出的Excel路径
string excelFile = sfdReporters.FileName;
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
#region 生成详细页
//创建一张数据表
DataTable dt = new DataTable();
#region 添加列字段
dt.Columns.Add("领域", typeof(string));
dt.Columns.Add("技术方向代码", typeof(string));
dt.Columns.Add("技术方向名称", typeof(string));
dt.Columns.Add("项目名称", typeof(string));
dt.Columns.Add("密级", typeof(string));
dt.Columns.Add("关键词", typeof(string));
dt.Columns.Add("单位名称", typeof(string));
dt.Columns.Add("单位常用名", typeof(string));
dt.Columns.Add("联系人", typeof(string));
dt.Columns.Add("联系电话", typeof(string));
dt.Columns.Add("项目负责人", typeof(string));
dt.Columns.Add("项目负责人电话", typeof(string));
dt.Columns.Add("身份证号", typeof(string));
dt.Columns.Add("通信地址", typeof(string));
dt.Columns.Add("研究周期", typeof(string));
dt.Columns.Add("研究经费", typeof(string));
dt.Columns.Add("研究目标", typeof(string));
dt.Columns.Add("项目摘要", typeof(string));
dt.Columns.Add("基础性问题", typeof(string));
string[] chsNumbers = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" };
for (int k = 0; k < 10; k++)
{
dt.Columns.Add("课题" + chsNumbers[k] + "名称", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "密级", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "负责人", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "身份证号", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "承担单位名称", typeof(string));
dt.Columns.Add("课题" + chsNumbers[k] + "研究经费", typeof(string));
}
dt.Columns.Add("项目负责人具体情况", typeof(string));
dt.Columns.Add("与有关计划关系", typeof(string));
#endregion
//拼凑数据
List <Catalog> nowCatalogs = ConnectionManager.Context.table("Catalog").select("*").getList <Catalog>(new Catalog());
foreach (Catalog catalog in nowCatalogs)
{
//项目信息
Project mainProj = ConnectionManager.Context.table("Project").where ("CatalogID = '" + catalog.CatalogID + "' and Type='项目'").select("*").getItem <Project>(new Project());
//承担单位
Unit mainUnit = ConnectionManager.Context.table("Unit").where ("ID = '" + mainProj.UnitID + "' and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Unit>(new Unit());
//项目负责人
Person mainPerson = ConnectionManager.Context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and CatalogID = '" + catalog.CatalogID + "') and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Person>(new Person());
if (string.IsNullOrEmpty(mainProj.ID) || string.IsNullOrEmpty(mainUnit.ID) || string.IsNullOrEmpty(mainPerson.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")信息不全无法改出！");
//信息不全
continue;
}
else
{
//拼装每个项目的信息
List <object> cells = new List <object>();
cells.Add(mainProj.Domain);
cells.Add(mainProj.DirectionCode);
cells.Add(mainProj.Direction);
cells.Add(mainProj.Name);
cells.Add(mainProj.SecretLevel);
cells.Add(mainProj.Keywords);
cells.Add(mainUnit.UnitName);
cells.Add(mainUnit.NormalName);
cells.Add(mainUnit.ContactName);
cells.Add(mainUnit.Telephone);
cells.Add(mainPerson.Name);
cells.Add(mainPerson.MobilePhone);
cells.Add(mainPerson.IDCard);
cells.Add(mainPerson.Address);
cells.Add(mainProj.TotalTime);
cells.Add(mainProj.TotalMoney);
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_4.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_0.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_5.rtf"));
//创建课题信息
List <Project> subjectList = ConnectionManager.Context.table("Project").where ("Type='课题' and CatalogID = '" + catalog.CatalogID + "'").select("*").getList <Project>(new Project());
foreach (Project sub in subjectList)
{
Unit subUnits = ConnectionManager.Context.table("Unit").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + sub.UnitID + "'").select("*").getItem <Unit>(new Unit());
Task subTasks = ConnectionManager.Context.table("Task").where ("Role = '负责人' and CatalogID = '" + catalog.CatalogID + "' and ProjectID = '" + sub.ID + "'").select("*").getItem <Task>(new Task());
if (string.IsNullOrEmpty(subUnits.ID) || string.IsNullOrEmpty(subTasks.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全！");
//信息不全
continue;
}
else
{
Person subPersons = ConnectionManager.Context.table("Person").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + subTasks.PersonID + "'").select("*").getItem <Person>(new Person());
if (string.IsNullOrEmpty(subPersons.ID))
{
MessageBox.Show("对不起，项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全！");
//信息不全
continue;
}
else
{
cells.Add(sub.Name);
cells.Add(sub.SecretLevel);
cells.Add(subPersons.Name);
cells.Add(subPersons.IDCard);
cells.Add(subUnits.UnitName);
cells.Add(subTasks.TotalMoney);
}
}
}
//给其它的课题信息项填充空格
for (int kk = 0; kk < (60 - (subjectList.Count * 6)); kk++)
{
cells.Add(string.Empty);
}
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_14.rtf"));
cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_19.rtf"));
dt.Rows.Add(cells.ToArray());
}
}
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#endregion
//输出到Excel文件
workbook.Write(memoryStream);
File.WriteAllBytes(excelFile, memoryStream.ToArray());
MessageBox.Show("导出完成！路径：" + excelFile, "提示");
//打开Excel文件
System.Diagnostics.Process.Start(excelFile);
}
catch (Exception ex)
{
MessageBox.Show("对不起，导出失败！Ex:" + ex.ToString());
}
}
}
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(strValue);
cell.CellStyle = style;
}
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style = null)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(strValue);
if (style != null)
{
cell.CellStyle = style;
}
else
{
cell.CellStyle.BorderBottom = 0;
}
}
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName)
{
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName);
List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>();
foreach (DataGridViewColumn i in dataGridView1.Columns)
{
if (i.Visible == true)
{
ListColumns.Add(i);
}
}
if (dataGridView1.Rows.Count <= 0)
{
return(false);
}
foreach (DataGridViewColumn dc in ListColumns)
{
if (dc.Visible == false)
{
continue;
}
if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double))
{
c.SetColumnWidth(dc.Index, 10 * 256);
}
else
{
c.SetColumnWidth(dc.Index, 20 * 256);
}
}
#region 表头
NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0);
var FirstCell = RowHeader.CreateCell(0);
FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName);
NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle();
cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont();
CellFontHeader.FontName = "微软雅黑";
CellFontHeader.FontHeightInPoints = 16;
cellstyleHeader.SetFont(CellFontHeader);
cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
FirstCell.CellStyle = cellstyleHeader;
c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1));
#endregion
#region 标题行 居中并且有框线
NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle();
CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1);
int cindex = 0;
foreach (DataGridViewColumn hc in ListColumns)
{
if (!hc.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex);
cindex++;
cell.CellStyle = CellStyleTitles;
if (!string.IsNullOrEmpty(hc.HeaderText))
{
cell.SetCellValue(hc.HeaderText);
}
}
#endregion
#region 列表 有框线,默认左对齐
NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle();
CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
foreach (DataGridViewRow i in dataGridView1.Rows)
{
NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2);
cindex = 0;
foreach (DataGridViewCell col in i.Cells)
{
if (!col.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex);
cindex++;
//设置居中对齐，如果是string则左对齐
if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid))
{
xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格
}
else
{
xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格，与标题栏一致
}
if (col.Value == null)
{
continue;
}
if (col.ValueType == typeof(string))
{
xcell.SetCellValue(col.Value.ToString());
}
else
if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double))
{
xcell.SetCellValue(double.Parse(col.Value.ToString()));
}
else
{
xcell.SetCellValue(col.Value.ToString());
}
}
}
#endregion
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel电子表格|*.xls";
sfd.FileName = tableName + DateTime.Now.Ticks.ToString();
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName))
{
wb.Write(fs);
MessageBox.Show("导出成功！");
}
}
catch (System.IO.IOException ex)
{
MessageBox.Show("导出失败！\n" + ex.Message);
return(false);
}
}
}
return(true);
}
protected void btnExport_Click(object sender, EventArgs e)
{
//http://www.itnose.net/detail/476834.html
//http://blog.csdn.net/gjban/article/details/39030669
var list = GetData();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_workplan.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("工作计划表");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 内容
int i = 3;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(i - 2);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.SheepNo);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue(o.Project.Name);
NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3);
cell3.CellStyle = style;
cell3.SetCellValue(o.WorkRemark);
NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4);
cell4.CellStyle = style;
cell4.SetCellValue(o.PlanTypeStr);
NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5);
cell5.CellStyle = style;
cell5.SetCellValue(Common.St.ToDateTimeString(o.StartTime, "yyyy-MM-dd"));
NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6);
cell6.CellStyle = style;
cell6.SetCellValue(Common.St.ToDateTimeString(o.RealStartTime, "yyyy-MM-dd"));
NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7);
cell7.CellStyle = style;
cell7.SetCellValue(Common.St.ToDateTimeString(o.EndTime, "yyyy-MM-dd"));
NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8);
cell8.CellStyle = style;
cell8.SetCellValue(Common.St.ToDateTimeString(o.RealEndTime, "yyyy-MM-dd"));
NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9);
cell9.CellStyle = style;
cell9.SetCellValue(o.StateStr);
NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10);
cell10.CellStyle = style;
cell10.SetCellValue(o.Needer.RealName);
NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11);
cell11.CellStyle = style;
cell11.SetCellValue(o.Remark);
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("工作计划表", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
private NPOI.SS.UserModel.ICellStyle CloneStyle()
{
if (myStyle == null)
{
myStyle = this._cell.Sheet.Workbook.CreateCellStyle();
myStyle.BorderBottom = this._cell.CellStyle.BorderBottom;
myStyle.BorderLeft = this._cell.CellStyle.BorderLeft;
myStyle.BorderRight = this._cell.CellStyle.BorderRight;
myStyle.BorderTop = this._cell.CellStyle.BorderTop;
myStyle.FillForegroundColor = this._cell.CellStyle.FillForegroundColor;
myStyle.FillPattern = this._cell.CellStyle.FillPattern;
}
return myStyle;
}
protected void btnExport_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"];
string strActivityType = Session["ActivityType"].ToString();
Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn);
if (activity == null)
{
Response.Redirect("~/View/Mutual/HomePage.aspx");
conn.Close();
return;
}
Dal.Models.Prize prize = BLL.Prize.GetPrize(Convert.ToInt32(hfPrizeID.Value), conn);
List <Dal.Models.VoteResult> lstVoteResult = BLL.Vote.GetVoteResult(prize.PrizeID.Value, prize.MultipleVoteRound ?? 1, conn);
DataTable dtExpert = Dal.DataTableExtensions.ToDataTable(lstVoteResult);
string root = System.Web.HttpContext.Current.Request.PhysicalApplicationPath;
string TempletFileName = root + "Content/Template/投票.xls"; //模板文件
string ReportFileName = Server.MapPath("~/Content/Temp/ExcelTemp/" + prize.PrizeName + "投票结果.xls"); //导出文件
try
{
NPOI.HSSF.UserModel.HSSFWorkbook wk = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet ws = wk.CreateSheet(prize.PrizeName + "投票结果");
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICellStyle style = wk.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
row = ws.CreateRow(0);
row.HeightInPoints = 35;
SetNewCell(row, 0, "排序", style);
SetNewCell(row, 1, "项目编码", style);
SetNewCell(row, 2, "项目名称", style);
SetNewCell(row, 3, "申报单位", style);
SetNewCell(row, 4, "申报奖项", style);
SetNewCell(row, 5, "奖项类别", style);
SetNewCell(row, 6, "投票方式", style);
if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" ||
((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1))
{
SetNewCell(row, 7, "推荐", style);
SetNewCell(row, 8, "不推荐", style);
SetNewCell(row, 9, "缓评", style);
SetNewCell(row, 10, "转出", style);
}
else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2)
{
SetNewCell(row, 7, "一等奖", style);
SetNewCell(row, 8, "二等奖", style);
SetNewCell(row, 9, "三等奖", style);
}
else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304"))
{
SetNewCell(row, 7, "一等奖", style);
SetNewCell(row, 8, "二等奖", style);
SetNewCell(row, 9, "三等奖", style);
SetNewCell(row, 10, "不推荐", style);
SetNewCell(row, 11, "缓评", style);
SetNewCell(row, 12, "转出", style);
}
else
{
if (prize.MultipleVoteRound == 2)
{
SetNewCell(row, 7, "一等奖、二等奖", style);
SetNewCell(row, 8, "三等奖", style);
}
else
{
SetNewCell(row, 7, "一等奖", style);
SetNewCell(row, 8, "二等奖", style);
}
}
for (int i = 0; i < dtExpert.Rows.Count; i++)
{
row = ws.CreateRow(i + 1);
SetNewCell(row, 0, dtExpert.Rows[i]["Ordinal"].ToString(), style);
SetNewCell(row, 1, dtExpert.Rows[i]["DeclarationCode"].ToString(), style);
SetNewCell(row, 2, dtExpert.Rows[i]["DeclarationName"].ToString(), style);
SetNewCell(row, 3, dtExpert.Rows[i]["OrganizationName"].ToString(), style);
SetNewCell(row, 4, dtExpert.Rows[i]["PrizeName"].ToString(), style);
SetNewCell(row, 5, dtExpert.Rows[i]["PrizeType"].ToString(), style);
SetNewCell(row, 6, dtExpert.Rows[i]["VoteTypeText"].ToString(), style);
if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" ||
((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1))
{
SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style);
SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
SetNewCell(row, 9, dtExpert.Rows[i]["SlowRating"].ToString(), style);
SetNewCell(row, 10, dtExpert.Rows[i]["TurnOut"].ToString(), style);
}
else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2)
{
SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style);
SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style);
SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style);
}
else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304"))
{
SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style);
SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style);
SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style);
SetNewCell(row, 10, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
SetNewCell(row, 11, dtExpert.Rows[i]["SlowRating"].ToString(), style);
SetNewCell(row, 12, dtExpert.Rows[i]["TurnOut"].ToString(), style);
}
else
{
SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style);
SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
}
}
ws.ForceFormulaRecalculation = true;
//先删除上一个临时文件
if (File.Exists(ReportFileName))
{
FileInfo fi = new FileInfo(ReportFileName);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
File.Delete(ReportFileName);
}
//创建临时文件写入数据
using (FileStream filess = File.OpenWrite(ReportFileName))
{
wk.Write(filess);
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
return;
}
System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(prize.PrizeName + "投票结果.xls"));
Response.AddHeader("Content-Length", filet.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(filet.FullName);
Response.End();
conn.Close();
}
protected void btnExport_Click(object sender, EventArgs e)
{
int year = Common.St.ToInt32(selYear.Value);
var m = 12;
if (year == DateTime.Today.Year)
{
m = DateTime.Today.Month;
}
var list = DAL.WorkPlanRule.Get().Where(a => a.NeederId > 0 && a.Needer.Status == 1).GroupBy(a => a.NeederId).Select(a =>
{
return(new
{
Name = a.First().Needer.RealName,
M1 = m < 1 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-2-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-2-1") || b.RealEndTime < DateTime.Parse(year + "-1-1"))))).Count(),
M2 = m < 2 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-3-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-3-1") || b.RealEndTime < DateTime.Parse(year + "-2-1"))))).Count(),
M3 = m < 3 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-4-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-4-1") || b.RealEndTime < DateTime.Parse(year + "-3-1"))))).Count(),
M4 = m < 4 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-5-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-5-1") || b.RealEndTime < DateTime.Parse(year + "-4-1"))))).Count(),
M5 = m < 5 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-6-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-6-1") || b.RealEndTime < DateTime.Parse(year + "-5-1"))))).Count(),
M6 = m < 6 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-7-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-7-1") || b.RealEndTime < DateTime.Parse(year + "-6-1"))))).Count(),
M7 = m < 7 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-8-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-8-1") || b.RealEndTime < DateTime.Parse(year + "-7-1"))))).Count(),
M8 = m < 8 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-9-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-9-1") || b.RealEndTime < DateTime.Parse(year + "-8-1"))))).Count(),
M9 = m < 9 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-10-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-10-1") || b.RealEndTime < DateTime.Parse(year + "-9-1"))))).Count(),
M10 = m < 10 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-11-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-11-1") || b.RealEndTime < DateTime.Parse(year + "-10-1"))))).Count(),
M11 = m < 11 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-12-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-12-1") || b.RealEndTime < DateTime.Parse(year + "-11-1"))))).Count(),
M12 = m < 12 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse((year + 1) + "-1-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse((year + 1) + "-1-1") || b.RealEndTime < DateTime.Parse(year + "-12-1"))))).Count()
});
});
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_4.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("测试人员负责项目数统计");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// 内容
int i = 1;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.M1);
NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue(o.M2);
NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3);
cell3.CellStyle = style;
cell3.SetCellValue(o.M3);
NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4);
cell4.CellStyle = style;
cell4.SetCellValue(o.M4);
NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5);
cell5.CellStyle = style;
cell5.SetCellValue(o.M5);
NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6);
cell6.CellStyle = style;
cell6.SetCellValue(o.M6);
NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7);
cell7.CellStyle = style;
cell7.SetCellValue(o.M7);
NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8);
cell8.CellStyle = style;
cell8.SetCellValue(o.M8);
NPOI.SS.UserModel.ICell cell9 = row.CreateCell(9);
cell9.CellStyle = style;
cell9.SetCellValue(o.M9);
NPOI.SS.UserModel.ICell cell10 = row.CreateCell(10);
cell10.CellStyle = style;
cell10.SetCellValue(o.M10);
NPOI.SS.UserModel.ICell cell11 = row.CreateCell(11);
cell11.CellStyle = style;
cell11.SetCellValue(o.M11);
NPOI.SS.UserModel.ICell cell12 = row.CreateCell(12);
cell12.CellStyle = style;
cell12.SetCellValue(o.M12);
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("测试人员负责项目数统计", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
