/// <summary>
/// 设置单元格内容
/// </summary>
/// <param name="row">单元格行标(从0开始，下同)</param>
/// <param name="col">单元格列标</param>
/// <param name="o">写入内容</param>
public void SetValue(int r, int c, object o)
{
if (o != null)
{
if (r <= osheet.LastRowNum)
{
IRow row = osheet.GetRow(r);
if (row == null)
{
row = osheet.CreateRow(r);
row.HeightInPoints = 14;
}
if (c <= row.LastCellNum)
{
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
else
{
for (int j = row.LastCellNum; j < c; j++)
{
row.CreateCell(j + 1);
ICell cell22 = row.GetCell(j + 1);
ICellStyle style = obook.CreateCellStyle();
cell22.CellStyle = style;
}
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
}
else
{
for (int i = osheet.LastRowNum; i < r; i++)
{
IRow row22 = osheet.CreateRow(i + 1);
row22.HeightInPoints = 14;
}
IRow row = osheet.GetRow(r);
for (int j = row.LastCellNum; j < c; j++)
{
row.CreateCell(j + 1);;
ICell cell22 = row.GetCell(j + 1);
ICellStyle style = obook.CreateCellStyle();
cell22.CellStyle = style;
}
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
}
}
public ICellStyle Cellstyle()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
IFont font = book.CreateFont();
font.FontName = "宋体";
//font.setFontHeightInPoints((short)10);
font.FontHeightInPoints = 10;
// 普通单元格样式
ICellStyle style = book.CreateCellStyle();
style.SetFont(font);
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
//style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//style.setWrapText(true);
//style.setLeftBorderColor(HSSFColor.BLACK.index);
//style.setBorderLeft((short)1);
//style.setRightBorderColor(HSSFColor.BLACK.index);
//style.setBorderRight((short)1);
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
//style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
return(style);
}
/// <summary>
/// Init
/// </summary>
/// <param name="Response"></param>
public void Init(HttpResponseBase Response)
{
this.Response = Response;
if (Book == null)
{
Book = new NPOI.HSSF.UserModel.HSSFWorkbook();
this.DataFormat = Book.CreateDataFormat();
this.DateCellStyle = Book.CreateCellStyle();
this.DateTimeCellStyle = Book.CreateCellStyle();
this.TimeCellStyle = Book.CreateCellStyle();
this.DateCellStyle.DataFormat = DataFormat.GetFormat("yyyy/MM/dd");
this.DateTimeCellStyle.DataFormat = DataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
this.TimeCellStyle.DataFormat = DataFormat.GetFormat("HH:mm:ss");
DefaultSheet = Book.CreateSheet("Sheet1");
}
}
public FileResult Export(Pagination rq, AdminCredential User)
{
#region 报表导出
var report = service.Get(rq.vid).Data;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 10; //字体大小
font.FontName = "宋体"; //和excel里面的字体对应
font.Boldweight = short.MaxValue; //字体加粗
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
style.SetFont(font); //将字体样式赋给样式对象
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 430;
var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
int i = 0;
foreach (var item in FieldList)
{
ICell cell = row1.CreateCell(i);
cell.SetCellValue(item.Title);
cell.CellStyle = style;
i++;
}
rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
DataTable dt = service.GetExportReportData(rq, User);
//将数据逐步写入sheet1各个行
for (int z = 0; z < dt.Rows.Count; z++)
{
var row = dt.Rows[z];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
int c = 0;
foreach (var item in FieldList)
{
rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));
#endregion
}
void CreateExcel(object sender)
{
try
{
string filePath = Application.StartupPath + "\\DailyPOSSales.xls";
if (System.IO.File.Exists(filePath))
{
File.Delete(filePath);
}
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Daily POS Sales");
//第1行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICell fistCell = row.CreateCell(0);
fistCell.SetCellValue("Daily POS Sales");
ICellStyle firstRowStyle = workbook.CreateCellStyle();
firstRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont firstRowFont = workbook.CreateFont();
firstRowFont.FontName = "Tahoma";
firstRowFont.FontHeightInPoints = 20;
firstRowFont.Color = HSSFColor.Blue.Index;
firstRowFont.Boldweight = short.MaxValue;
firstRowStyle.SetFont(firstRowFont);
fistCell.CellStyle = firstRowStyle;
row.HeightInPoints = 25;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15));
using (FileStream fs = File.OpenWrite(filePath))
{
workbook.Write(fs);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
//this.Invoke(new Action(() =>
//{
// (sender as Button).Enabled = true;
//}));
}
}
public static byte[] getExcelData(DataTable t
, GridColumns cols)
{
var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var sheet = workbook.CreateSheet("data");
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
var font1 = workbook.CreateFont();
font1.IsBold = true;
for (int i = 0; i < cols.Count; i++)
{
var col = cols[i];
var cell = row.CreateCell(i);
cell.SetCellValue(col.title);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font1);
sheet.SetColumnWidth(i, 5500);
}
foreach (DataRow r in t.Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (int iCol = 0; iCol < cols.Count; iCol++)
{
var col = cols[iCol];
var cell = row.CreateCell(iCol);
cell.SetCellValue(r[col.datafield].ToString());
//cell.CellStyle.GetFont(workbook).IsBold = false;
}
}
using (var fileData = new System.IO.MemoryStream())
{
workbook.Write(fileData);
return(g.ConvertStreamToByteArray(fileData));
}
}
public static MemoryStream ExportAuditLog(int zoneTableId, DateTime?startTime, DateTime?endTime, string logType, string detail)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//List<AuditLogEntity> list = new List<AuditLogEntity>();
//for (int i = 0; i < 50; i++)
//{
// AuditLogEntity en = new AuditLogEntity();
// en.ID = i;
// en.LogType = "App";
// en.Detail = "detail" + i;
// en.LogTime = DateTime.Now;
// en.LogOperator = "Michael.he";
// list.Add(en);
//}
List <AuditLogEntity> list = AuditLogAccess.GetList(zoneTableId, startTime, endTime, logType, detail);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("Log Type");
row1.CreateCell(1).SetCellValue("Detail");
row1.CreateCell(2).SetCellValue("Log Time");
row1.CreateCell(3).SetCellValue("Log Operator");
var notesStyle = book.CreateCellStyle();
notesStyle.WrapText = true;//设置换行这个要先设置
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
var rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].LogType.ToString());
rowtemp.CreateCell(1).SetCellValue(list[i].Detail.Replace("<br />", "\n"));
rowtemp.Cells[1].CellStyle = notesStyle;
rowtemp.CreateCell(2).SetCellValue(list[i].LogTime.ToString(CultureInfo.InvariantCulture));
rowtemp.CreateCell(3).SetCellValue(list[i].LogOperator);
}
sheet1.SetColumnWidth(1, 160 * 256);
sheet1.SetColumnWidth(2, 20 * 256);
// 写入到客户端
var ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
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();
}
///// <summary>
///// 樣式的值是否改變
///// </summary>
//internal bool StyleValueIsChange
//{
// get { return _styleValueIsChange; }
// set{_styleValueIsChange =value;}
//}
#endregion
#region 方法
internal ICellStyle GetCellStyle(NPOI.HSSF.UserModel.HSSFWorkbook workBook)
{
if (workBook == null)
{
throw new ArgumentNullException("workBook參數不能為NULL。");
}
if (_cellStyle == null || _styleValueIsChange)
{
_cellStyle = workBook.CreateCellStyle();
IFont font = workBook.FindFont((short)_fontBold, _fontColor, (short)(_fontSize * 20), _fontName, _isItalic, _isStrikeout, _fontTypeOffset, (byte)_underline);
if (font == null)
{
font = workBook.CreateFont();
font.FontName = this._fontName;
font.Boldweight = (short)this._fontBold;
font.FontHeightInPoints = (short)this._fontSize;
font.Underline = (byte)this.UnderLine;
font.IsItalic = _isItalic;
font.Color = _fontColor;
font.IsStrikeout = _isStrikeout;
font.TypeOffset = _fontTypeOffset;
}
_cellStyle.SetFont(font);
IDataFormat format = workBook.CreateDataFormat();
_cellStyle.BorderBottom = this._borderBottom;
_cellStyle.BorderLeft = this._borderLeft;
_cellStyle.BorderRight = this._borderRight;
_cellStyle.BorderTop = this._borderTop;
_cellStyle.Alignment = this._alignment;
_cellStyle.VerticalAlignment = this.VerticalAlignment;
if (!string.IsNullOrEmpty(_dataFormart))
{
_cellStyle.DataFormat = format.GetFormat(this._dataFormart);
}
_cellStyle.WrapText = this._warpText;
_styleValueIsChange = false;
}
return(_cellStyle);
}
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();
}
/// <summary>
/// 将DataGridView中的数据，从起始列开始，原样导出到EXCEL中
/// </summary>
/// <param name="mView">源DataGridView</param>
/// <param name="startIndex">起始列序号</param>
/// <returns>0-成功，1-失败</returns>
static public int Deport(System.Windows.Forms.DataGridView mView, int startIndex)
{
try
{
IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
ICellStyle styleRight = mWorkbook.CreateCellStyle();
//ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
//IDataFormat format = mWorkbook.CreateDataFormat();
styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
//NumberStyle.DataFormat = format.GetFormat("0.00");
ICell mCell;
int i, j;
int x = 0;
//DataGridView mView = dgvReport;
//添加标头
IRow mRow = mSheet.CreateRow(0);
for (i = startIndex; i <= mView.Columns.Count - 1; i++)
{
if (mView.Columns[i].Visible == true)
{
mCell = mRow.CreateCell(x);
x++;
mCell.SetCellValue(mView.Columns[i].HeaderText);
mCell.CellStyle = styleRight;
}
}
//添加内容
for (i = 1; i <= mView.RowCount; i++)
{
mRow = mSheet.CreateRow(i);
x = 0;
for (j = startIndex; j <= mView.Columns.Count - 1; j++)
{
if (mView.Columns[j].Visible == true)
{
mCell = mRow.CreateCell(x);
x++;
if (PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true)
{
mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value));
}
else
{
mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value));
}
mCell.CellStyle = styleRight;
}
}
}
//创建文件
SaveFileDialog mDialog = new SaveFileDialog();
mDialog.AddExtension = true;
mDialog.DefaultExt = "xls";
mDialog.Filter = "Excel Worksheets(*.xls)|*.xls";
mDialog.InitialDirectory = System.Environment.CurrentDirectory;
mDialog.FileName = "导出数据-" + DateTime.Now.ToString("yyyyMMddhhmmss");
if (mDialog.ShowDialog() == DialogResult.OK)
{
Stream mFile = mDialog.OpenFile();
mWorkbook.Write(mFile);
mFile.Close();
MessageBox.Show("保存成功！", "提示");
return(0);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return(1);
}
public byte[] exportToExcel(DataSet source)
{
var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var sheet = workbook.CreateSheet("data");
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
var font1 = workbook.CreateFont();
font1.IsBold = true;
for (int i = 0; i < cols.Count; i++)
{
var col = cols[i];
var cell = row.CreateCell(i);
cell.SetCellValue(col.title);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font1);
sheet.SetColumnWidth(i, 5500);
}
foreach (DataRow r in source.Tables[0].Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (int iCol = 0; iCol < cols.Count; iCol++)
{
var col = cols[iCol];
var cell = row.CreateCell(iCol);
string sVal = "";
if (r[col.datafield] is DateTime && col.Format.isEmpty() == false)
{
sVal = r.getDateFormat(col.datafield, col.Format);
}
else
{
sVal = r[col.datafield].ToString();
}
cell.SetCellValue(sVal);
}
}
using (var fileData = new System.IO.MemoryStream())
{
workbook.Write(fileData);
return(g.ConvertStreamToByteArray(fileData));
}
}
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);
}
static public int InvoiceDeport(System.Windows.Forms.DataGridView mView)
{
try
{
IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
ICellStyle styleRight = mWorkbook.CreateCellStyle();
//ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
//IDataFormat format = mWorkbook.CreateDataFormat();
styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
styleRight.WrapText = true;
ICell mCell;
int i;
//添加标头
IRow mRow = mSheet.CreateRow(0);
int m = 0;
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("公司名称");
mSheet.SetDefaultColumnStyle(m, styleRight);//设置样式
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("总金额");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("发票类型");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("开票日期");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("商品名称");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("单价");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("数量");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("金额");
mSheet.SetDefaultColumnStyle(m, styleRight);
//添加内容
int mExcelRowCount = 1;
for (i = 0; i <= mView.RowCount - 1; i++)
{
if (Convert.ToBoolean(mView.Rows[i].Cells["ColCombine"].Value) == true)
{
mRow = mSheet.CreateRow(mExcelRowCount++);
string InvoiceContent = string.Empty;
//公司名称
mCell = mRow.CreateCell(0);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["customerNameDataGridViewTextBoxColumn"].Value));
//总金额
mCell = mRow.CreateCell(1);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceMoneyDataGridViewTextBoxColumn"].Value));
//发票类型
mCell = mRow.CreateCell(2);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceTypeDataGridViewTextBoxColumn"].Value));
//开票日期
mCell = mRow.CreateCell(3);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceDateDataGridViewTextBoxColumn"].Value));
//开票内容
InvoiceContent = Convert.ToString(mView.Rows[i].Cells["invoiceContentDataGridViewTextBoxColumn"].Value);
string[] contents = System.Text.RegularExpressions.Regex.Split(InvoiceContent, "!#!");
for (int n = 0; n < contents.Length - 3; n += 4)
{
mCell = mRow.CreateCell(4);
mCell.SetCellValue(contents[n]);
mCell = mRow.CreateCell(5);
mCell.SetCellValue(contents[n + 1]);
mCell = mRow.CreateCell(6);
mCell.SetCellValue(contents[n + 2]);
mCell = mRow.CreateCell(7);
mCell.SetCellValue(contents[n + 3]);
if (n < contents.Length - 7)
{
mRow = mSheet.CreateRow(mExcelRowCount++);
}
}
mSheet.CreateRow(mExcelRowCount++);
}
}
//创建文件
SaveFileDialog mDialog = new SaveFileDialog();
mDialog.AddExtension = true;
mDialog.DefaultExt = "xls";
mDialog.Filter = "Excel Worksheets(*.xls)|*.xls";
mDialog.InitialDirectory = System.Environment.CurrentDirectory;
mDialog.FileName = "开票-" + DateTime.Now.ToString("yyyyMMddhhmmss");
if (mDialog.ShowDialog() == DialogResult.OK)
{
Stream mFile = mDialog.OpenFile();
mWorkbook.Write(mFile);
mFile.Close();
MessageBox.Show("保存成功！", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return(0);
}
protected void ExportButton_Click(object sender, EventArgs e)
{
ProductSearchInfo productSearch = new ProductSearchInfo();
productSearch.IsSale = (int)BoolType.True;
productSearch.Name = ShopCommon.ConvertToT <string>(Name.Text);
productSearch.ClassId = ShopCommon.ConvertToT <string>(ClassID.Text);
productSearch.BrandId = ShopCommon.ConvertToT <int>(BrandID.Text);
productSearch.StorageAnalyse = ShopCommon.ConvertToT <int>(StorageAnalyse.Text);
var data = ProductBLL.SearchList(1, 1000, productSearch, ref Count);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("商品名称");
row.CreateCell(1).SetCellValue("剩余库存量");
row.CreateCell(2).SetCellValue("库存下限");
row.CreateCell(3).SetCellValue("库存上限");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var entity in data)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
dataRow.CreateCell(0).SetCellValue(entity.Name);
dataRow.CreateCell(1).SetCellValue(ShowStorageCount(entity.TotalStorageCount - entity.SendCount, entity.ImportActualStorageCount));
dataRow.CreateCell(2).SetCellValue(Convert.ToString(entity.LowerCount));
dataRow.CreateCell(3).SetCellValue(Convert.ToString(entity.UpperCount));
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.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();
Response.End();
}
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();
}
private void btnExcel_Click(object sender, EventArgs e)
{
try
{
IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(Application.StartupPath + "\\office\\财务统计模板.xls", FileMode.Open, FileAccess.Read));
//IWorkbook mWorkbook =
//ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
ISheet mSheet = mWorkbook.GetSheet("采购明细表");
ICellStyle styleRight = mWorkbook.CreateCellStyle();
//ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
//IDataFormat format = mWorkbook.CreateDataFormat();
styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//NumberStyle.DataFormat = format.GetFormat("0.00");
ICell mCell;
int i, j;
DataGridView mView = dgvDaPurchaseContract;
//添加标头
IRow mRow;//= mSheet.CreateRow(0);
//for (i = 0; i <= mView.Columns.Count - 1; i++)
//{
// mCell = mRow.CreateCell(i);
// mCell.SetCellValue(mView.Columns[i].HeaderText);
// mCell.CellStyle = styleRight;
//}
//添加内容
for (i = 1; i <= mView.RowCount; i++)
{
mRow = mSheet.CreateRow(i + 2);
for (j = 0; j <= mView.Columns.Count - 1; j++)
{
if (mView.Columns[j].Visible == false)
{
continue;
}
mCell = mRow.CreateCell(j);
if (Classes.PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true && j != dpc单据号码.Index)
{
mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value));
}
else
{
mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value));
}
mCell.CellStyle = styleRight;
}
}
//创建文件
SaveFileDialog mDialog = new SaveFileDialog();
mDialog.AddExtension = true;
mDialog.DefaultExt = "xls";
mDialog.Filter = "Excel Worksheets(*.xls)|*.xls";
mDialog.InitialDirectory = System.Environment.CurrentDirectory;
mDialog.FileName = "财务采购统计-" + DateTime.Now.ToString("yyyyMMddhhmmss");
if (mDialog.ShowDialog() == DialogResult.OK)
{
Stream mFile = mDialog.OpenFile();
mWorkbook.Write(mFile);
mFile.Close();
MessageBox.Show("保存成功！", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
var userList = UserBLL.SearchList(new UserSearchInfo {
UserName = UserName.Text, ProviderNo = ProviderNo.Text, UserType = (int)UserType.Provider
});
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("供应商编号");
row.CreateCell(1).SetCellValue("用户名");
row.CreateCell(2).SetCellValue("电子邮箱");
row.CreateCell(3).SetCellValue("公司名称");
row.CreateCell(4).SetCellValue("对公银行开户行");
row.CreateCell(5).SetCellValue("税务号");
row.CreateCell(6).SetCellValue("法人代表");
row.CreateCell(7).SetCellValue("联系人");
row.CreateCell(8).SetCellValue("联系电话");
row.CreateCell(9).SetCellValue("传真");
row.CreateCell(10).SetCellValue("经营品牌");
row.CreateCell(11).SetCellValue("经营品类");
row.CreateCell(12).SetCellValue("结算方式");
row.CreateCell(13).SetCellValue("结算周期");
row.CreateCell(14).SetCellValue("物流配送");
row.CreateCell(15).SetCellValue("售后服务");
row.CreateCell(16).SetCellValue("退换货保障");
row.CreateCell(17).SetCellValue("详细地址");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var user in userList)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(userList.IndexOf(user) + 1);
dataRow.CreateCell(0).SetCellValue(user.ProviderNo);
dataRow.CreateCell(1).SetCellValue(user.UserName);
dataRow.CreateCell(2).SetCellValue(user.Email);
dataRow.CreateCell(3).SetCellValue(user.ProviderName);
dataRow.CreateCell(4).SetCellValue(user.ProviderBankNo);
dataRow.CreateCell(5).SetCellValue(user.ProviderTaxRegistration);
dataRow.CreateCell(6).SetCellValue(user.ProviderCorporate);
dataRow.CreateCell(7).SetCellValue(user.ProviderLinker);
dataRow.CreateCell(8).SetCellValue(user.ProviderLinkerTel);
dataRow.CreateCell(9).SetCellValue(user.ProviderFax);
dataRow.CreateCell(10).SetCellValue(user.ProviderOperateBrand);
dataRow.CreateCell(11).SetCellValue(user.ProviderOperateClass);
dataRow.CreateCell(12).SetCellValue(user.ProviderAccount);
dataRow.CreateCell(13).SetCellValue(user.ProviderAccountCycle);
dataRow.CreateCell(14).SetCellValue(user.ProviderShipping);
dataRow.CreateCell(15).SetCellValue(user.ProviderService);
dataRow.CreateCell(16).SetCellValue(user.ProviderEnsure);
dataRow.CreateCell(17).SetCellValue(RegionBLL.RegionNameList(user.RegionId) + ", " + user.ProviderAddress);
}
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();
Response.End();
}
public static void ExportFile(DataTable dt, string fileName, List <string> columnNames, int[] mergeCondition, int[] noMergeColumns, int[] wrapColumns, int [] numerberColumns)
{
using (var book = new NPOI.HSSF.UserModel.HSSFWorkbook())
{
var sheet = book.CreateSheet();
var headRow = sheet.CreateRow(0);
for (int i = 0; i < columnNames.Count; i++)
{
var cell = headRow.CreateCell(i);
cell.SetCellValue(columnNames[i]);
var font = book.CreateFont();
font.Boldweight = NPOI.HSSF.UserModel.HSSFFont.BOLDWEIGHT_BOLD;
cell.RichStringCellValue.ApplyFont(font);
}
foreach (var wrapC in wrapColumns)
{
NPOI.HSSF.UserModel.HSSFCellStyle cs = book.CreateCellStyle();
cs.WrapText = true;
sheet.SetDefaultColumnStyle((short)wrapC, cs);
}
//开始写入内容
int RowCount = dt.Rows.Count; //行数
int ColCount = columnNames.Count; //列数
for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
var row = sheet.CreateRow(rowIndex + 1);
for (int colIndex = 0; colIndex < ColCount; colIndex++)
{
var cell = row.CreateCell(colIndex);
if (IsWrapColumn(wrapColumns, colIndex))
{
cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString().Replace("</br>", "\n"));
}
else
{
cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
}
}
}
for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
for (int colIndex = 0; colIndex < ColCount; colIndex++)
{
if (noMergeColumns.Contains(colIndex))
{
continue;
}
var rowSpan = GetRowSpan(dt, mergeCondition, rowIndex, colIndex);
if (rowSpan <= 1)
{
continue;
}
sheet.AddMergedRegion(new NPOI.HSSF.Util.CellRangeAddress(rowIndex + 1, rowIndex + rowSpan, colIndex, colIndex));
}
}
////设置每列的宽度
//for (int colIndex = 0; colIndex < columnNames.Count; colIndex++)
//{
// sheet.AutoSizeColumn(colIndex);
// sheet.SetColumnWidth(colIndex, sheet.GetColumnWidth(colIndex) + 2100);
//}
ExportFile(book, fileName);
}
}
public static MemoryStream RenderToExcel(List<Models.Beans.Contract> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "存放位置", CellType.String, style);
CreateCell(row, 1, "合同号", CellType.String, style);
CreateCell(row, 2, "序号", CellType.String, style);
CreateCell(row, 3, "项目编号", CellType.String, style);
CreateCell(row, 4, "项目名称", CellType.String, style);
CreateCell(row, 5, "项目负责人", CellType.String, style);
CreateCell(row, 6, "联系方式", CellType.String, style);
CreateCell(row, 7, "分管部门", CellType.String, style);
CreateCell(row, 8, "分包名称", CellType.String, style);
CreateCell(row, 9, "分包预算(万元)", CellType.String, style);
CreateCell(row, 10, "招标编号", CellType.String, style);
CreateCell(row, 11, "招标公司", CellType.String, style);
CreateCell(row, 12, "开标时间", CellType.String, style);
CreateCell(row, 13, "付款方式", CellType.String, style);
CreateCell(row, 14, "中标公司名称", CellType.String, style);
CreateCell(row, 15, "联系人", CellType.String, style);
CreateCell(row, 16, "手机号码", CellType.String, style);
CreateCell(row, 17, "中标金额(万元)", CellType.String, style);
CreateCell(row, 18, "签合同日期", CellType.String, style);
CreateCell(row, 19, "交货时间", CellType.String, style);
CreateCell(row, 20, "验收情况", CellType.String, style);
CreateCell(row, 21, "进度", CellType.String, style);
CreateCell(row, 22, "支付全款", CellType.String, style);
CreateCell(row, 23, "押款", CellType.String, style);
CreateCell(row, 24, "退款", CellType.String, style);
CreateCell(row, 25, "标签", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Models.Beans.Contract model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.contractplace);
CreateCell(row, 1, model.contractnum);
CreateCell(row, 2, model.seq);
CreateCell(row, 3, model.projectnum);
CreateCell(row, 4, model.projectname);
CreateCell(row, 5, model.projectmanager);
CreateCell(row, 6, model.tel);
CreateCell(row, 7, model.depart);
CreateCell(row, 8, model.packageName);
CreateCell(row, 9, model.packageBudget);
CreateCell(row, 10, model.tendarNum);
CreateCell(row, 11, model.tendarCompany);
CreateCell(row, 12, model.tendarStartTime);
CreateCell(row, 13, model.paymethod);
CreateCell(row, 14, model.bcompany);
CreateCell(row, 15, model.linker);
CreateCell(row, 16, model.phone);
CreateCell(row, 17, model.money);
CreateCell(row, 18, model.signingdate);
CreateCell(row, 19, model.deliveryTime);
CreateCell(row, 20, model.inspection);
CreateCell(row, 21, model.progress);
CreateCell(row, 22, model.isPayAll);
CreateCell(row, 23, model.isArmoured);
CreateCell(row, 24, model.isRefund);
CreateCell(row, 25, model.contractrfid);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
public JsonResult ExportMember(string keyword = "")
{
var list = _repositoryFactory.IMemberCard.Where(c => true);
//搜索关键字过滤
if (!string.IsNullOrEmpty(keyword))
{
list = list.Where(c => c.CardNo.Contains(keyword) || c.Mobile.Contains(keyword));
}
var data = list.OrderByDescending(m => m.CreateTime).ToList();
if (data.Count > 0)
{
//NPOI导出数据
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
ICellStyle style1 = book.CreateCellStyle();
style1.Alignment = HorizontalAlignment.Center;
style1.VerticalAlignment = VerticalAlignment.Center;
ICellStyle style2 = book.CreateCellStyle();
style2.Alignment = HorizontalAlignment.Left;
style2.VerticalAlignment = VerticalAlignment.Center;
IRow headerrow = sheet.CreateRow(0);
ICell cell_1 = headerrow.CreateCell(0);
cell_1.CellStyle = style1;
cell_1.SetCellValue("会员卡号");
ICell cell_2 = headerrow.CreateCell(1);
cell_2.CellStyle = style1;
cell_2.SetCellValue("手机号");
ICell cell_3 = headerrow.CreateCell(2);
cell_3.CellStyle = style1;
cell_3.SetCellValue("姓名");
ICell cell_4 = headerrow.CreateCell(3);
cell_4.CellStyle = style1;
cell_4.SetCellValue("性别");
ICell cell_5 = headerrow.CreateCell(4);
cell_5.CellStyle = style1;
cell_5.SetCellValue("创建时间");
ICell cell_6 = headerrow.CreateCell(5);
cell_6.CellStyle = style1;
cell_6.SetCellValue("创建人");
ICell cell_7 = headerrow.CreateCell(6);
cell_7.CellStyle = style1;
cell_7.SetCellValue("状态");
ICell cell_8 = headerrow.CreateCell(7);
cell_8.CellStyle = style1;
cell_8.SetCellValue("可用余额");
ICell cell_9 = headerrow.CreateCell(8);
cell_9.CellStyle = style1;
cell_9.SetCellValue("累计充值");
foreach (var item in data)
{
IRow headerrow_1 = sheet.CreateRow(data.IndexOf(item) + 1);
ICell cell_1_1 = headerrow_1.CreateCell(0);
cell_1_1.CellStyle = style1;
cell_1_1.SetCellValue(item.CardNo);
ICell cell_1_2 = headerrow_1.CreateCell(1);
cell_1_2.CellStyle = style1;
cell_1_2.SetCellValue(item.Mobile);
ICell cell_1_3 = headerrow_1.CreateCell(2);
cell_1_3.CellStyle = style1;
cell_1_3.SetCellValue(item.Name);
ICell cell_1_4 = headerrow_1.CreateCell(3);
cell_1_4.CellStyle = style1;
cell_1_4.SetCellValue(item.Sex);
ICell cell_1_5 = headerrow_1.CreateCell(4);
cell_1_5.CellStyle = style1;
cell_1_5.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.CreateTime));
ICell cell_1_6 = headerrow_1.CreateCell(5);
cell_1_6.CellStyle = style1;
cell_1_6.SetCellValue(item.CreateUser);
ICell cell_1_7 = headerrow_1.CreateCell(6);
cell_1_7.CellStyle = style1;
cell_1_7.SetCellValue(EnumHepler.GetEnumDescription(((EnumHepler.MemberCardStatus)item.Status)));
ICell cell_1_8 = headerrow_1.CreateCell(7);
cell_1_8.CellStyle = style1;
cell_1_8.SetCellValue(string.Format("{0:N2}", item.Banlance));
ICell cell_1_9 = headerrow_1.CreateCell(8);
cell_1_9.CellStyle = style1;
cell_1_9.SetCellValue(string.Format("{0:N2}", item.TotalMoney));
}
sheet.SetColumnWidth(1, 30 * 150);
sheet.SetColumnWidth(3, 30 * 250);
string fileName = "会员卡列表_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
string filePath = HttpContext.Server.MapPath("/Upload/Export/" + fileName);
using (FileStream fs = System.IO.File.OpenWrite(filePath))
{
book.Write(fs);//向打开的这个xls文件中写入并保存。
}
return(Json(new TipMessage()
{
Status = true, MsgText = "导出成功！", Url = Url.Action("DownLoadFile", "FileHandler", new { path = filePath, content = "application/ms-excel" })
}, JsonRequestBehavior.DenyGet));
}
else
{
return(Json(new TipMessage()
{
Status = false, MsgText = "暂无会员卡记录！"
}, JsonRequestBehavior.DenyGet));
}
}
public FileResult ExportData(string state, string key)
{
string _title = "运行管理.xls";
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
SetCellRangeAddress(sheet1, 0, 1, 0, 0);
SetCellRangeAddress(sheet1, 0, 1, 1, 1);
SetCellRangeAddress(sheet1, 0, 1, 2, 2);
SetCellRangeAddress(sheet1, 0, 1, 3, 3);
SetCellRangeAddress(sheet1, 0, 1, 4, 4);
SetCellRangeAddress(sheet1, 0, 1, 5, 5);
SetCellRangeAddress(sheet1, 0, 0, 6, 8);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1);
SetStyle(cellstyle, row1, 0, "盒子编号");
SetStyle(cellstyle, row1, 1, "状态");
SetStyle(cellstyle, row1, 2, "省\\直辖市");
SetStyle(cellstyle, row1, 3, "领用人");
SetStyle(cellstyle, row1, 4, "学校");
SetStyle(cellstyle, row1, 5, "首次回执");
SetStyle(cellstyle, row1, 6, "版本");
SetStyle(cellstyle, row2, 6, "英语");
SetStyle(cellstyle, row2, 7, "数学");
SetStyle(cellstyle, row2, 8, "语文");
List <VM_BoxRunStat_Index_Grid> _list = new List <VM_BoxRunStat_Index_Grid>();
using (var db = new box_omsEntities())
{
var query = db.box_good.OrderBy(o => o.Code).AsQueryable();
if (!string.IsNullOrWhiteSpace(key))
{
if (System.Text.RegularExpressions.Regex.IsMatch(key, @"^[0-9]+$"))
{
int _id = Convert.ToInt32(key);
query = query.Where(w => w.BoxId == _id);
}
else
{
query = query.Where(w => w.SchoolName.Contains(key) || w.UseUserName.Contains(key));
}
}
if (!string.IsNullOrWhiteSpace(state))
{
int _state = Convert.ToInt32(state);
query = query.Where(w => w.State == _state);
}
_list = query.Select(s => new VM_BoxRunStat_Index_Grid
{
BoxId = s.BoxId,
SchoolName = s.SchoolName,
FirstRunTime = s.FirstRunTime,
UseUserName = s.UseUserName,
State = s.State
}).ToList();
}
if (_list.Count > 0)
{
for (int i = 0; i < _list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 2);
string _state = string.Empty;
switch (_list[i].State)
{
case 1:
_state = "库存";
break;
case 2:
_state = "已售";
break;
case 3:
_state = "领用";
break;
}
string _FirstRunTime = _list[i].FirstRunTime == null ? "" : ((DateTime)_list[i].FirstRunTime).ToString("yyyy-MM-dd HH:mm:ss");
rowtemp.CreateCell(0).SetCellValue(_list[i].BoxId);
rowtemp.CreateCell(1).SetCellValue(_state);
rowtemp.CreateCell(2).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackPrName(_list[i].BoxId));
rowtemp.CreateCell(3).SetCellValue(_list[i].UseUserName);
rowtemp.CreateCell(4).SetCellValue(_list[i].SchoolName);
rowtemp.CreateCell(5).SetCellValue(_FirstRunTime);
rowtemp.CreateCell(6).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 3));
rowtemp.CreateCell(7).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 2));
rowtemp.CreateCell(8).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 1));
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", _title));
}
protected void ExportButton_Click(object sender, EventArgs e)
{
//if (Cookies.Admin.GetAdminName(true).ToLower() != "admin") {
// //只有admin才有权限导出会员数据
// ScriptHelper.Alert("没有权限");
//}
//else
//{
UserSearchInfo userSearch = new UserSearchInfo();
userSearch.UserName = RequestHelper.GetQueryString <string>("UserName");
userSearch.Mobile = RequestHelper.GetQueryString <string>("Mobile");
userSearch.Status = RequestHelper.GetQueryString <int>("Status");
userSearch.StartRegisterDate = RequestHelper.GetQueryString <DateTime>("StartRegisterDate");
userSearch.EndRegisterDate = ShopCommon.SearchEndDate(RequestHelper.GetQueryString <DateTime>("EndRegisterDate"));
UserName.Text = userSearch.UserName;
Mobile.Text = userSearch.Mobile;
StartRegisterDate.Text = RequestHelper.GetQueryString <string>("StartRegisterDate");
EndRegisterDate.Text = RequestHelper.GetQueryString <string>("EndRegisterDate");
status = userSearch.Status;
var userList = UserBLL.SearchList(new UserSearchInfo
{
UserName = userSearch.UserName,
CardNo = userSearch.CardNo,
Status = userSearch.Status
});
var data = userList.Take(1000).ToList();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("用户名");
row.CreateCell(1).SetCellValue("性别");
row.CreateCell(2).SetCellValue("手机");
row.CreateCell(3).SetCellValue("邮箱");
row.CreateCell(4).SetCellValue("固定电话");
row.CreateCell(5).SetCellValue("QQ");
row.CreateCell(6).SetCellValue("生日");
row.CreateCell(7).SetCellValue("所在地");
row.CreateCell(8).SetCellValue("注册时间");
row.CreateCell(9).SetCellValue("登录次数");
row.CreateCell(10).SetCellValue("最近登录");
row.CreateCell(11).SetCellValue("会员状态");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var entity in data)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
dataRow.CreateCell(0).SetCellValue(entity.UserName);
dataRow.CreateCell(1).SetCellValue(entity.Sex == 1 ? "男" : entity.Sex == 2 ? "女" : "保密");
dataRow.CreateCell(2).SetCellValue(entity.Mobile);
dataRow.CreateCell(3).SetCellValue(entity.Mobile);
dataRow.CreateCell(4).SetCellValue(entity.Tel);
dataRow.CreateCell(5).SetCellValue(entity.QQ);
dataRow.CreateCell(6).SetCellValue(entity.Birthday);
dataRow.CreateCell(7).SetCellValue(RegionBLL.ReadCityName(entity.RegionId) + entity.Address);
dataRow.CreateCell(8).SetCellValue(entity.RegisterDate.ToString());
dataRow.CreateCell(9).SetCellValue(entity.LoginTimes);
dataRow.CreateCell(10).SetCellValue(entity.LastLoginDate.ToString());
dataRow.CreateCell(11).SetCellValue(entity.Status == 1 ? "未验证" : entity.Status == 2 ? "正常" : "冻结");
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.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();
Response.End();
//}
}
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();
}
private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("EmpID", typeof(int));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("EmpName", typeof(string));
dataTable.Columns.Add("EmpAlias", typeof(string));
dataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("TrainingSeminarID", typeof(int));
dataTable.Columns.Add("TrainingCourseName", typeof(string));
dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));
foreach (EEmpPersonalInfo empInfo in empList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);
ECompany company = new ECompany();
EPosition position = new EPosition();
ArrayList empHierarchyList = new ArrayList();
if (empPos != null)
{
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
//row["Company"] = company.CompanyCode;
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
//foreach (EEmpHierarchy empHierarchy in empHierarchyList)
//{
// EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
// if (hierarchyLevel != null)
// {
// EHierarchyElement hierarchyElement = new EHierarchyElement();
// hierarchyElement.HElementID = empHierarchy.HElementID;
// if (EHierarchyElement.db.select(dbConn, hierarchyElement))
// row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
// }
//}
position.PositionID = empPos.PositionID;
EPosition.db.select(dbConn, position);
}
DBFilter empTrainingSeminar = new DBFilter();
empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));
OR orTrainingCourse = null;
foreach (ListItem item in TrainingCourseList.Items)
{
if (item.Selected)
{
if (orTrainingCourse == null)
{
orTrainingCourse = new OR();
}
orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
}
}
DBFilter trainingSeminarFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
}
if (orTrainingCourse != null)
{
trainingSeminarFilter.add(orTrainingCourse);
}
empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
{
ETrainingSeminar trainingSeminar = new ETrainingSeminar();
trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
{
DataRow row = dataTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["EmpNo"] = empInfo.EmpNo;
row["EmpName"] = empInfo.EmpEngFullName;
row["EmpAlias"] = empInfo.EmpAlias;
row["Company"] = company.CompanyCode;
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
row["Position"] = position.PositionDesc;
row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
ETrainingCourse trainingCourse = new ETrainingCourse();
trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
if (ETrainingCourse.db.select(dbConn, trainingCourse))
{
row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
}
else
{
row["TrainingCourseName"] = string.Empty;
}
row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo;
row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
{
row["TrainingSeminarDurationUnit"] = "Hour(s)";
}
else
{
row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
}
row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;
dataTable.Rows.Add(row);
}
}
}
}
//org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
//org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleCenter.SetFont(boldFont);
columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
int rowCount = 0;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
reportHeaderCell.SetCellValue("Training Report");
reportHeaderCell.CellStyle = reportHeaderStyle;
rowCount++;
if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodFrom.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
rowCount++;
}
if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
int currentTrainingSeminarID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
{
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
}
currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
//worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
//worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
int currentEmpID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentEmpID.Equals((int)row["EmpID"]))
{
//worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());
colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
currentEmpID = ((int)row["EmpID"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
}
}
else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
//worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//document.FileName = exportFileName;
//document.Save();
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";
WebUtils.TransmitFile(Response, exportFileName, filename, true);
return;
}
private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName)
{
int columnCount = 0;
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport");
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Set column width
worksheet.SetColumnWidth(0, 40 * 256);
worksheet.SetColumnWidth(2, 20 * 256);
worksheet.SetColumnWidth(5, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 12 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex);
headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export");
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1);
headerRow.CreateCell(0).SetCellValue(peroid);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3);
foreach (DataColumn headercolumn in tmpDataTable.Columns)
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount);
cell.SetCellValue(headercolumn.ColumnName);
if (columnCount == 11)
{
cell.CellStyle = style;
}
columnCount++;
}
// Set value for every row
foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4);
detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString());
detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString());
detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString());
detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString());
detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString());
detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString());
detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString());
detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString());
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.SetCellValue((DateTime)row[FIELD_FROM]);
cell.CellStyle = dateCellStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.SetCellValue((DateTime)row[FIELD_TO]);
cell.CellStyle = dateCellStyle;
detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString());
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
cell.SetCellValue((double)row[FIELD_PERCENTAGE]);
cell.CellStyle = numericStyle;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.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();
}
/// <summary>
/// 导出问卷数据
/// </summary>
/// <param name="list"></param>
public string CreateExcel()
{
list = (from r in _db.korea_record
join emp in _db.korea_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId }
join ans in
(
(from a in _db.korea_answer
group a by new
{
a.rId
} into g
select new
{
arid = g.Key.rId.Value,
answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)),
answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)),
answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)),
answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)),
answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)),
answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)),
answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)),
answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)),
answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)),
answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)),
answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)),
answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null))
}))on new { RId = r.rId } equals new { RId = ans.arid }
orderby
r.rId
select new RecordModel
{
AnswerId = r.rId,
AnswerName = emp.eName,
AnswerNumber = emp.eNumber,
AnswerPhone = emp.ePhone,
AnswerDuration = r.rDuration.Value,
AnswerTime = r.rEndtime.Value,
AnswerAnswer1 = ans.answer1,
AnswerAnswer2 = ans.answer2,
AnswerAnswer3 = ans.answer3,
AnswerAnswer4 = ans.answer4,
AnswerAnswer5 = ans.answer5,
AnswerAnswer6 = ans.answer6,
AnswerAnswer7 = ans.answer7,
AnswerAnswer8 = ans.answer8,
AnswerAnswer9 = ans.answer9,
AnswerAnswer10 = ans.answer10,
AnswerAnswer11 = ans.answer11,
AnswerAnswer12 = ans.answer12,
}
).ToList();
if (list != null && list.Count > 0) //
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("问卷数据下载");
//
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 18 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 25 * 256);
ICellStyle style = book.CreateCellStyle();
HSSFColor color = new HSSFColor.BLACK();
FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景
HSSFColor backGround = new HSSFColor.GREY_25_PERCENT();
IFont font = ExportData.GetFontStyle(book, "宋体", color, 11);
//设置单元格的样式：水平对齐居中
style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
ICell cell1 = rowHeader.CreateCell(0);
cell1.SetCellValue("姓名");
//将新的样式赋给单元格
cell1.CellStyle = style;
ICell cell2 = rowHeader.CreateCell(1);
cell2.SetCellValue("编号");
cell2.CellStyle = style;
ICell cell3 = rowHeader.CreateCell(2);
cell3.SetCellValue("手机号码");
cell3.CellStyle = style;
ICell cell4 = rowHeader.CreateCell(3);
cell4.SetCellValue("第1题");
cell4.CellStyle = style;
ICell cell5 = rowHeader.CreateCell(4);
cell5.SetCellValue("第2题");
cell5.CellStyle = style;
ICell cell6 = rowHeader.CreateCell(5);
cell6.SetCellValue("第3题");
cell6.CellStyle = style;
ICell cell7 = rowHeader.CreateCell(6);
cell7.SetCellValue("第4题");
cell7.CellStyle = style;
ICell cell8 = rowHeader.CreateCell(7);
cell8.SetCellValue("第5题");
cell8.CellStyle = style;
ICell cell9 = rowHeader.CreateCell(8);
cell9.SetCellValue("第6题");
cell9.CellStyle = style;
ICell cell10 = rowHeader.CreateCell(9);
cell10.SetCellValue("第7题");
cell10.CellStyle = style;
ICell cell11 = rowHeader.CreateCell(10);
cell11.SetCellValue("第8题");
cell11.CellStyle = style;
ICell cell12 = rowHeader.CreateCell(11);
cell12.SetCellValue("第9题");
cell12.CellStyle = style;
ICell cell13 = rowHeader.CreateCell(12);
cell13.SetCellValue("第10题");
cell13.CellStyle = style;
ICell cell14 = rowHeader.CreateCell(13);
cell14.SetCellValue("第11题");
cell14.CellStyle = style;
ICell cell15 = rowHeader.CreateCell(14);
cell15.SetCellValue("第12题");
cell15.CellStyle = style;
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont rowfont = book.CreateFont();
rowfont.FontName = "宋体";
rowfont.FontHeightInPoints = (short)11;
rowfont.Color = color.GetIndex();
cellStyle.SetFont(rowfont);
//
NPOI.SS.UserModel.IRow row = null;
for (int i = 0; i < list.Count; i++)
{
RecordModel model = list[i];
row = sheet.CreateRow(i + 1);
ICell rowcell1 = row.CreateCell(0);
rowcell1.SetCellValue(model.AnswerName);
rowcell1.CellStyle = cellStyle;
ICell rowcell2 = row.CreateCell(1);
rowcell2.SetCellValue(model.AnswerNumber);
rowcell2.CellStyle = cellStyle;
ICell rowcell3 = row.CreateCell(2);
rowcell3.SetCellValue(model.AnswerPhone);
rowcell3.CellStyle = cellStyle;
ICell rowcell4 = row.CreateCell(3);
rowcell4.SetCellValue(model.AnswerAnswer1);
rowcell4.CellStyle = cellStyle;
ICell rowcell5 = row.CreateCell(4);
rowcell5.SetCellValue(model.AnswerAnswer2);
rowcell5.CellStyle = cellStyle;
ICell rowcell6 = row.CreateCell(5);
rowcell6.SetCellValue(model.AnswerAnswer3);
rowcell6.CellStyle = cellStyle;
ICell rowcell7 = row.CreateCell(6);
rowcell7.SetCellValue(model.AnswerAnswer4);
rowcell7.CellStyle = cellStyle;
ICell rowcell8 = row.CreateCell(7);
rowcell8.SetCellValue(model.AnswerAnswer5);
rowcell8.CellStyle = cellStyle;
ICell rowcell9 = row.CreateCell(8);
rowcell9.SetCellValue(model.AnswerAnswer6);
rowcell9.CellStyle = cellStyle;
ICell rowcell10 = row.CreateCell(9);
rowcell10.SetCellValue(model.AnswerAnswer7);
rowcell10.CellStyle = cellStyle;
ICell rowcell11 = row.CreateCell(10);
rowcell11.SetCellValue(model.AnswerAnswer8);
rowcell11.CellStyle = cellStyle;
ICell rowcell12 = row.CreateCell(11);
rowcell12.SetCellValue(model.AnswerAnswer9);
rowcell12.CellStyle = cellStyle;
ICell rowcell13 = row.CreateCell(12);
rowcell13.SetCellValue(model.AnswerAnswer10);
rowcell13.CellStyle = cellStyle;
ICell rowcell14 = row.CreateCell(13);
rowcell14.SetCellValue(model.AnswerAnswer11);
rowcell14.CellStyle = cellStyle;
ICell rowcell15 = row.CreateCell(14);
rowcell15.SetCellValue(model.AnswerAnswer12);
rowcell15.CellStyle = cellStyle;
}
//
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
//string path = @"C:\Users\DNS\Desktop\导出参会人";
//string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel";
// path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel";
string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\";
DirectoryInfo excelDir = new DirectoryInfo(path);
//如果路径不存在就创建
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH ");
path = excelDir.FullName + fileName;
if (File.Exists(path))
{
File.Delete(path);
}
using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
//ms.ToArray()转换为字节数组就是想要的图片源字节
localFile.Write(ms.ToArray(), 0, (int)ms.Length);
}
book = null;
ms.Close();
ms.Dispose();
//
string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString();
return(webAddress + fileName);
}
}
catch (Exception)
{
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>");
return("Error1");
}
}
else
{
return("Error2");
}
}
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();
}
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);
}
/// <summary>
/// 返回与EasyUI的datagrid相适应的分页数据
/// <para>此方法将直接返回分页结果</para>
/// </summary>
/// <typeparam name="T">结果集类型</typeparam>
/// <param name="result">分页结果集</param>
/// <returns>执行结果</returns>
//protected ActionResult PagerDirectly<T>(PagedResult<T> result)
//{
// return Json(new { total = result.TotalItems, rows = result.Items }, JsonRequestBehavior.AllowGet);
//}
///// <summary>
///// 根据存储过程返回查询数据
///// </summary>
///// <param name="procedureName"></param>
///// <param name="filters"></param>
///// <returns></returns>
//public IList<IDictionary<string, object>> GetDataByProcedure(string procedureName, List<MyFilter> filters = null)
//{
// filters = filters == null ? new List<MyFilter>() : filters;
// var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
// var cmd = new OracleCommand(procedureName, conn);
// var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();
// cmd.CommandType = CommandType.StoredProcedure;
// //var paramers = new HashSet<DbParameter>();
// foreach (var item in paramsList)
// {
// var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
// if (filter == null)
// {
// continue;
// }
// if (OracelTypeDic.ContainsKey(filter.dataType))
// {
// var param = new OracleParameter()
// {
// OracleDbType = OracelTypeDic[filter.dataType],
// ParameterName = filter.property,
// Direction = ParameterDirection.Input
// };
// if (filter.value == null)
// {
// param.Value = DBNull.Value;
// }
// else if (filter.dataType == "date")
// {
// param.Value = DateTime.Parse(filter.value);
// }
// else
// {
// param.Value = filter.value;
// }
// cmd.Parameters.Add(param);
// }
// }
// var paramer1 = new OracleParameter()
// {
// OracleDbType = OracleDbType.RefCursor,
// ParameterName = "DATATABLE",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer1);
// var da = new OracleDataAdapter(cmd);
// da.SelectCommand = cmd;
// DataSet ds = new DataSet("dt");
// da.Fill(ds, "dt");
// return ds.Tables[0].ToIDictionaryList();
//}
///// <summary>
///// 根据存储过程返回查询数据
///// </summary>
///// <param name="procedureName"></param>
///// <param name="filters"></param>
///// <returns></returns>
//public IList<IDictionary<string, object>> GetPagedByProcedure(string procedureName, int page, int rows, out int total, List<MyFilter> filters = null)
//{
// filters = filters == null ? new List<MyFilter>() : filters;
// filters.Add(new MyFilter() { property = "PAGE", value = page.ToString(), dataType = "number" });
// filters.Add(new MyFilter() { property = "ROWS", value = rows.ToString(), dataType = "number" });
// var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
// var cmd = new OracleCommand(procedureName, conn);
// var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();
// cmd.CommandType = CommandType.StoredProcedure;
// foreach (var item in paramsList)
// {
// var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
// if (filter == null)
// {
// continue;
// }
// if (OracelTypeDic.ContainsKey(filter.dataType))
// {
// var param = new OracleParameter()
// {
// OracleDbType = OracelTypeDic[filter.dataType],
// ParameterName = filter.property,
// Direction = ParameterDirection.Input
// };
// if (filter.value == null)
// {
// param.Value = DBNull.Value;
// }
// else if (filter.dataType == "date")
// {
// param.Value = DateTime.Parse(filter.value);
// }
// else
// {
// param.Value = filter.value;
// }
// cmd.Parameters.Add(param);
// }
// }
// var paramer1 = new OracleParameter()
// {
// OracleDbType = OracleDbType.RefCursor,
// ParameterName = "DATATABLE",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer1);
// var paramer2 = new OracleParameter()
// {
// OracleDbType = OracleDbType.Int64,
// ParameterName = "TOTAL",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer2);
// DataSet ds = new DataSet("dt");
// total = 0;
// try
// {
// conn.Open();
// cmd.ExecuteNonQuery();
// var da = new OracleDataAdapter();
// da.Fill(ds, "dt", paramer1.Value as Oracle.ManagedDataAccess.Types.OracleRefCursor);
// total = int.Parse(paramer2.Value.ToString());
// }
// catch (Exception ex)
// {
// }
// finally
// {
// conn.Close();
// }
// return ds.Tables[0].ToIDictionaryList();
//}
///// <summary>
///// OracleDbType类型转换
///// </summary>
//private static Dictionary<string, OracleDbType> OracelTypeDic = new Dictionary<string, OracleDbType>
//{
// { "varchar", OracleDbType.Varchar2 },
// { "nvarchar", OracleDbType.NVarchar2 },
// { "number", OracleDbType.Int64 },
// { "date", OracleDbType.Date },
// { "long", OracleDbType.Long }
//};
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="data">数据集</param>
/// <param name="columns">列</param>
/// <param name="sheetName">导出的Excel文件名称</param>
/// <returns></returns>
public FileResult ExportExcel(List <Dictionary <string, object> > data, List <Column> columns, string sheetName = "导出Excel")
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
columns = columns.Where(x => x.hidden != true && !string.IsNullOrEmpty(x.title)).ToList();
var headerStyle = book.CreateCellStyle();
var headerFont = book.CreateFont();
headerFont.IsBold = true;
headerFont.FontHeightInPoints = 12;
headerFont.FontName = "宋体";
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.SetFont(headerFont);
for (var i = 0; i < columns.Count; i++)
{
var cell = row1.CreateCell(i);
cell.SetCellValue(columns[i].title);
sheet1.SetColumnWidth(i, columns[i].width * 40);
cell.CellStyle = headerStyle;
}
var contentStyle = book.CreateCellStyle();
contentStyle.CloneStyleFrom(headerStyle);
var contentFont = book.CreateFont();
contentFont.FontHeightInPoints = 12;
contentFont.FontName = "宋体";
contentStyle.SetFont(contentFont);
for (int i = 0; i < data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (var j = 0; j < columns.Count; j++)
{
//var property = data[i].GetType().GetProperty(columns[j].field);
var value = "";
if (data[i].ContainsKey(columns[j].field) && data[i][columns[j].field] != null)
{
value = data[i][columns[j].field].ToString();
}
var cell = rowtemp.CreateCell(j);
cell.SetCellValue(value);
cell.CellStyle = contentStyle;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", sheetName + ".xls"));
}
protected void ExportButton_Click(object sender, EventArgs e)
{
ProductSearchInfo productSearch = new ProductSearchInfo();
OrderSearchInfo orderSearch = new OrderSearchInfo();
productSearch.IsSale = (int)BoolType.True;
productSearch.Name = ShopCommon.ConvertToT <string>(Name.Text);
productSearch.ClassId = ShopCommon.ConvertToT <string>(ClassID.Text);
productSearch.BrandId = ShopCommon.ConvertToT <int>(BrandID.Text);
orderSearch.StartAddDate = ShopCommon.ConvertToT <DateTime>(StartAddDate.Text);
orderSearch.EndAddDate = ShopCommon.SearchEndDate(ShopCommon.ConvertToT <DateTime>(EndAddDate.Text));
orderSearch.UserName = ShopCommon.ConvertToT <string>(UserName.Text);
orderSearch.OrderNumber = ShopCommon.ConvertToT <string>(OrderNumber.Text);
var data = OrderDetailBLL.StatisticsSaleDetail(1, 1000, orderSearch, productSearch, ref Count);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("时间");
row.CreateCell(1).SetCellValue("单号");
row.CreateCell(2).SetCellValue("商品名称");
row.CreateCell(3).SetCellValue("数量");
row.CreateCell(4).SetCellValue("金额");
row.CreateCell(5).SetCellValue("用户名");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (DataRow dr in data.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.Rows.IndexOf(dr) + 1);
dataRow.CreateCell(0).SetCellValue(Convert.ToString(dr["AddDate"]));
dataRow.CreateCell(1).SetCellValue(Convert.ToString(dr["OrderNumber"]));
dataRow.CreateCell(2).SetCellValue(Convert.ToString(dr["Name"]));
dataRow.CreateCell(3).SetCellValue(Convert.ToString(dr["BuyCount"]));
dataRow.CreateCell(4).SetCellValue(Convert.ToString(dr["Money"]));
dataRow.CreateCell(5).SetCellValue(Convert.ToString(dr["UserName"]));
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.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();
Response.End();
}
public static MemoryStream RenderToExcel(List<Bean.ArchiveBean> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "序号", CellType.String, style);
CreateCell(row, 1, "责任人", CellType.String, style);
CreateCell(row, 2, "文件题目", CellType.String, style);
CreateCell(row, 3, "页数", CellType.String, style);
CreateCell(row, 4, "编号", CellType.String, style);
CreateCell(row, 5, "备注", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Bean.ArchiveBean model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.idx);
CreateCell(row, 1, model.manager);
CreateCell(row, 2, model.title);
CreateCell(row, 3, model.pages);
CreateCell(row, 4, model.number);
CreateCell(row, 5, model.remark);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}