void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead)
{
if (iSheet == null)
{
throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。");
}
NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName);
}
int startRowIndex = 0;
if (firstRowIsColumnHead)
{
startRowIndex = 1;
}
for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++)
{
iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]);
}
}
}
static void DoMergeExcelSheet(NPOI.SS.UserModel.ISheet sheet)
{
string[] heads = new string[] { "姓名", "复训带教", "复训检查", "复训翻译" };
//增加列头
//设置列头直接进行单元格合并
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 1024;
for (int i = 0; i < heads.Length; i++)
{
int cellIndex = i > 0 ? 2 * i - 1 : i;
NPOI.SS.UserModel.ICell cell = row.CreateCell(cellIndex);
cell.SetCellValue(heads[i]);
}
for (int i = 0; i < heads.Length; i++)
{
if (i == 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
}
else
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 2 * i - 1, 2 * i));
}
}
}
public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue)
{
if (iCell == null)
{
return;
}
if (cellValue != null)
{
if (cellValue.GetType() == typeof(DBNull))
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else if (cellValue.GetType() == typeof(bool))
{
iCell.SetCellValue((bool)cellValue);
}
else if (cellValue.GetType() == typeof(string))
{
if (cellValue.ToString() == "#NUM!")
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else
{
iCell.SetCellValue((string)cellValue);
}
}
else if (cellValue.GetType() == typeof(DateTime))
{
NPOI.SS.UserModel.ICellStyle iCellStyle = iCell.Sheet.Workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat();
iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
iCell.CellStyle = iCellStyle;
iCell.SetCellValue((DateTime)cellValue);
}
else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float))
{
iCell.SetCellValue((double)cellValue);
}
else
{
iCell.SetCellValue(cellValue.ToString());
}
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 读取模板，根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="ModUrl">模板地址</param>
/// <param name="startRowIndex">起始行,首行=0，默认0</param>
/// <param name="RemoveOtherRows">是否移除起始行后的数据，默认=是</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1")
{
startRowIndex = startRowIndex >= 0?startRowIndex:0;
string ExcelModePath = NodeBase + ModUrl;
if (!File.Exists(ExcelModePath))
{
return(null);
}
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read))
{
wk = new XSSFWorkbook(fileExcelMod);
fileExcelMod.Close();
}
if (wk == null)
{
return(null);
}
//创建一个Sheet
ISheet tb = wk.GetSheetAt(0);
#region 移除起始行后的所有行
if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1)
{
for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--)
{
tb.ShiftRows(rmr, rmr + 1, -1);
}
}
#endregion
//设置单元格宽度
//创建表头(在第0行)
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + startRowIndex);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToStr());
SetBorder(cell);
}
}
#endregion
return(wk);
}
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style = null)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(strValue);
if (style != null)
{
cell.CellStyle = style;
}
else
{
cell.CellStyle.BorderBottom = 0;
}
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
/// <summary>
/// 根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//设置单元格宽度
for (int i = 0; i < columnWidth.Length; i++)
{
tb.SetColumnWidth(i, columnWidth[i] * 256);
}
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
//背景
//cell.CellStyle.FillForegroundColor = NPOI.XSSF.Util.XSSFColor.DarkTeal.Index;
//cell.CellStyle.FillPattern = FillPattern.SolidForeground;
//边框颜色
//cell.CellStyle.TopBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.LeftBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.BottomBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.RightBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
cell.CellStyle.IsLocked = true;
SetBorder(cell);
}
#endregion
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToStr());
SetBorder(cell);
}
}
#endregion
return(wk);
}
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();
}
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>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j >= columnName.Length)
{
break;
}
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt)
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet("Sheet1");
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 根据Datable表头创建Excel表头
for (int i = 0; i < dt.Columns.Count; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
//cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
//cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index;
cell.SetCellValue(dt.Columns[i].ColumnName);
tb.AutoSizeColumn(i);//自动调整宽度，貌似对中文支持不好
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName)
{
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName);
List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>();
foreach (DataGridViewColumn i in dataGridView1.Columns)
{
if (i.Visible == true)
{
ListColumns.Add(i);
}
}
if (dataGridView1.Rows.Count <= 0)
{
return(false);
}
foreach (DataGridViewColumn dc in ListColumns)
{
if (dc.Visible == false)
{
continue;
}
if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double))
{
c.SetColumnWidth(dc.Index, 10 * 256);
}
else
{
c.SetColumnWidth(dc.Index, 20 * 256);
}
}
#region 表头
NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0);
var FirstCell = RowHeader.CreateCell(0);
FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName);
NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle();
cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont();
CellFontHeader.FontName = "微软雅黑";
CellFontHeader.FontHeightInPoints = 16;
cellstyleHeader.SetFont(CellFontHeader);
cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
FirstCell.CellStyle = cellstyleHeader;
c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1));
#endregion
#region 标题行 居中并且有框线
NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle();
CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1);
int cindex = 0;
foreach (DataGridViewColumn hc in ListColumns)
{
if (!hc.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex);
cindex++;
cell.CellStyle = CellStyleTitles;
if (!string.IsNullOrEmpty(hc.HeaderText))
{
cell.SetCellValue(hc.HeaderText);
}
}
#endregion
#region 列表 有框线,默认左对齐
NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle();
CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
foreach (DataGridViewRow i in dataGridView1.Rows)
{
NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2);
cindex = 0;
foreach (DataGridViewCell col in i.Cells)
{
if (!col.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex);
cindex++;
//设置居中对齐，如果是string则左对齐
if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid))
{
xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格
}
else
{
xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格，与标题栏一致
}
if (col.Value == null)
{
continue;
}
if (col.ValueType == typeof(string))
{
xcell.SetCellValue(col.Value.ToString());
}
else
if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double))
{
xcell.SetCellValue(double.Parse(col.Value.ToString()));
}
else
{
xcell.SetCellValue(col.Value.ToString());
}
}
}
#endregion
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel电子表格|*.xls";
sfd.FileName = tableName + DateTime.Now.Ticks.ToString();
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName))
{
wb.Write(fs);
MessageBox.Show("导出成功！");
}
}
catch (System.IO.IOException ex)
{
MessageBox.Show("导出失败！\n" + ex.Message);
return(false);
}
}
}
return(true);
}
protected void 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 explort()
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
//this.gridView1.ExportToXls(sflg.FileName);
//NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_001");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataGridViewColumn item in this.dataGridView2.Columns)
{
if (item.Visible)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.HeaderText);
index++;
}
}
// 添加数据
for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataGridViewColumn item in this.dataGridView2.Columns)
{
if (item.Visible)
{
if (dataGridView2.Rows[i].Cells[item.HeaderText].Value != null)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dataGridView2.Rows[i].Cells[item.HeaderText].Value.ToString().Trim());
}
index++;
}
}
}
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
private void sb_save_Click(object sender, EventArgs e)
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
int aa = 0;
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
foreach (XtraTabPage trp in this.xtraTabControl1.TabPages)
{
XtraTabPage xinka = trp;
try
{
this.active_nepCalaTable = xinka.Controls[0] as NepCalaTable;
}
catch (Exception err)
{
MessageBox.Show("出现了错误： " + err.Message);
}
aa += 1;
//this.gridView1.ExportToXls(sflg.FileName);
//NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(string.Format("test_{0}", aa));
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xinka.Text);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
GridView ac = this.active_nepCalaTable.GC;
int count = ac.Columns.Count;
for (int i = 0; i < count; i++)
{
string Caption = ac.Columns[i].Caption;
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(Caption);
index++;
}
// 添加数据
DataTable dt = this.active_nepCalaTable.DDT;
for (int j = 0; j < dt.Rows.Count; j++)
{
index = 0;
row = sheet.CreateRow(j + 1);
for (int k = 0; k < count; k++)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
//cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString());
cell.SetCellValue(dt.Rows[j][k].ToString());
index++;
}
}
NPOI.SS.UserModel.IRow r1 = sheet.GetRow(0);
// 第一排
NPOI.SS.UserModel.ICell cell1 = r1.CreateCell(12);
cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
cell1.SetCellValue("激发点距");
NPOI.SS.UserModel.ICell cell11 = r1.CreateCell(13);
cell11.SetCellType(NPOI.SS.UserModel.CellType.String);
cell11.SetCellValue(this.active_nepCalaTable.jifa_dianju.Text);
// 第二排
r1 = sheet.GetRow(1);
NPOI.SS.UserModel.ICell cell2 = r1.CreateCell(12);
cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
cell2.SetCellValue("接收点距");
NPOI.SS.UserModel.ICell cell21 = r1.CreateCell(13);
cell21.SetCellType(NPOI.SS.UserModel.CellType.String);
cell21.SetCellValue(this.active_nepCalaTable.jieshou_dianju.Text);
// 第三排
r1 = sheet.GetRow(2);
NPOI.SS.UserModel.ICell cell3 = r1.CreateCell(12);
cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
cell3.SetCellValue("接收点数");
NPOI.SS.UserModel.ICell cell31 = r1.CreateCell(13);
cell31.SetCellType(NPOI.SS.UserModel.CellType.String);
cell31.SetCellValue(this.active_nepCalaTable.jieshou_dianshu.Text);
// 第四排
r1 = sheet.GetRow(3);
NPOI.SS.UserModel.ICell cell4 = r1.CreateCell(12);
cell4.SetCellType(NPOI.SS.UserModel.CellType.String);
cell4.SetCellValue("激发线距");
NPOI.SS.UserModel.ICell cell41 = r1.CreateCell(13);
cell41.SetCellType(NPOI.SS.UserModel.CellType.String);
cell41.SetCellValue(this.active_nepCalaTable.jifa_xianju.Text);
// 第五排
r1 = sheet.GetRow(4);
NPOI.SS.UserModel.ICell cell5 = r1.CreateCell(12);
cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
cell5.SetCellValue("接受线距");
NPOI.SS.UserModel.ICell cell51 = r1.CreateCell(13);
cell51.SetCellType(NPOI.SS.UserModel.CellType.String);
cell51.SetCellValue(this.active_nepCalaTable.jieshou_xianju.Text);
// 第六排
r1 = sheet.GetRow(5);
NPOI.SS.UserModel.ICell cell6 = r1.CreateCell(12);
cell6.SetCellType(NPOI.SS.UserModel.CellType.String);
cell6.SetCellValue("纵向滚动距离");
NPOI.SS.UserModel.ICell cell61 = r1.CreateCell(13);
cell61.SetCellType(NPOI.SS.UserModel.CellType.String);
cell61.SetCellValue(this.active_nepCalaTable.muban_zong.Text);
// 第7排
r1 = sheet.GetRow(6);
NPOI.SS.UserModel.ICell cell7 = r1.CreateCell(12);
cell7.SetCellType(NPOI.SS.UserModel.CellType.String);
cell7.SetCellValue("模板炮");
NPOI.SS.UserModel.ICell cell71 = r1.CreateCell(13);
cell71.SetCellType(NPOI.SS.UserModel.CellType.String);
cell71.SetCellValue(this.active_nepCalaTable.mobanpao.Text);
// 第8排
r1 = sheet.GetRow(7);
NPOI.SS.UserModel.ICell cell8 = r1.CreateCell(12);
cell8.SetCellType(NPOI.SS.UserModel.CellType.String);
cell8.SetCellValue("接收线数");
NPOI.SS.UserModel.ICell cell81 = r1.CreateCell(13);
cell81.SetCellType(NPOI.SS.UserModel.CellType.String);
cell81.SetCellValue(this.active_nepCalaTable.jieshou_xianshu.Text);
// 第9排
r1 = sheet.GetRow(8);
NPOI.SS.UserModel.ICell cell9 = r1.CreateCell(12);
cell9.SetCellType(NPOI.SS.UserModel.CellType.String);
cell9.SetCellValue("横向滚动距离");
NPOI.SS.UserModel.ICell cell91 = r1.CreateCell(13);
cell91.SetCellType(NPOI.SS.UserModel.CellType.String);
cell91.SetCellValue(this.active_nepCalaTable.muban_heng.Text);
// 第10排
r1 = sheet.GetRow(9);
NPOI.SS.UserModel.ICell cell10 = r1.CreateCell(12);
cell10.SetCellType(NPOI.SS.UserModel.CellType.String);
cell10.SetCellValue("布设接受线数");
NPOI.SS.UserModel.ICell cell101 = r1.CreateCell(13);
cell101.SetCellType(NPOI.SS.UserModel.CellType.String);
cell101.SetCellValue(this.active_nepCalaTable.bushe_jieshouxianshu.Text);
// 第11排
r1 = sheet.GetRow(10);
NPOI.SS.UserModel.ICell cell1_1 = r1.CreateCell(12);
cell1_1.SetCellType(NPOI.SS.UserModel.CellType.String);
cell1_1.SetCellValue("布设激发线数");
NPOI.SS.UserModel.ICell cell111 = r1.CreateCell(13);
cell111.SetCellType(NPOI.SS.UserModel.CellType.String);
cell111.SetCellValue(this.active_nepCalaTable.bushe_jifaxianshu.Text);
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
book = null;
MessageBox.Show("保存成功了");
}
public void DownlodExcel()
{
SaveFileDialog sflg = new SaveFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
sflg.FileName = "采购单细节导入模板表.xls";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("采购细节表");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("品名");
#region 医疗器械(这里不用)
//if ((int)this.toolStripComboBox1.ComboBox.SelectedValue == (int)PurchaseDrugTypes.医疗器械)
//{
// cell = row.CreateCell(1);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("型号");
// cell = row.CreateCell(2);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("规格");
// cell = row.CreateCell(3);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("单位");
// cell = row.CreateCell(4);
// cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
// cell.SetCellValue("生产厂家");
// cell = row.CreateCell(5);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("数量");
// cell = row.CreateCell(6);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("单价");
// cell = row.CreateCell(7);
// cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
// cell.SetCellValue("税率(%)");
//}
//else
#endregion
{
cell = row.CreateCell(1);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("剂型");
cell = row.CreateCell(2);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("规格");
cell = row.CreateCell(3);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("单位");
cell = row.CreateCell(4);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("生产厂家");
cell = row.CreateCell(5);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue("产地");
cell = row.CreateCell(6);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("数量");
cell = row.CreateCell(7);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("单价");
cell = row.CreateCell(8);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue("税率(%)");
}
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
private async void executeExportCommand(object obj)
{
IsRunning = true;
await Task.Run(() =>
{
try
{
reader.CheckPermission();
var filePath = Path.Combine(reader.GetFolderPath(), "SmsExport-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx");
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
//第一行
{
var row = sheet.CreateRow(0);
var cColumnInd = 0;
row.CreateCell(cColumnInd).SetCellValue("编号");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("号码");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("时间");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("类型");
cColumnInd++;
row.CreateCell(cColumnInd).SetCellValue("内容");
cColumnInd++;
}
//当前行号
var cRowInd = 1;
var totalCount = reader.GetCount(StartIndex);
var enumerator = reader.GetEnumerator(StartIndex);
var index = 0;
while (enumerator.MoveNext())
{
index++;
var item = enumerator.Current;
var typeStr = "";
switch (item.ItemType)
{
case SmsItem.SmsItemType.Send:
typeStr = "发送";
break;
case SmsItem.SmsItemType.Recv:
typeStr = "接收";
break;
default:
continue;
}
{
var cColumnInd = 0;
var row = sheet.CreateRow(cRowInd);
NPOI.SS.UserModel.ICell cell = null;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Id);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Address);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Date.ToString("yyyy-MM-dd HH:mm:ss"));
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(typeStr);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
cell = row.CreateCell(cColumnInd);
cell.SetCellValue(item.Body);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cColumnInd++;
}
cRowInd++;
Progress = index * 1D / totalCount;
Message = $"读取短信[{index} / {totalCount}]中...";
}
Message = $"正在保存到文件...";
using (var stream = File.OpenWrite(filePath))
workbook.Write(stream);
Message = $"已保存到{filePath}";
}
catch (Exception ex)
{
Message = "错误," + ex;
}
});
IsRunning = false;
}
/// <summary>
/// 插入到excel的cell
/// </summary>
/// <param name="fileName">excel文件名</param>
/// <param name="dst">目标 (行，列，值)</param>
/// <param name="sheetName">默认sheetName,可以不写</param>
public static void WriteToExcelCell(string fileName, List <Tuple <int, int, object, string> > customCell, string sheetName = "sheet1")
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
foreach (var item in customCell)
{
int r = item.Item1;
int c = item.Item2;
object content = item.Item3;
NPOI.SS.UserModel.IRow row = null;
if (sheet.GetRow(r) == null)
{
row = sheet.CreateRow(r);
}
else
{
row = sheet.GetRow(r);
}
NPOI.SS.UserModel.ICell cell = row.CreateCell(c);
cell.SetCellValue(content.ToString());
string objType = item.Item4.ToString();
#region 类型转换
try
{
switch (objType)
{
case "System.String": //字符串类型
cell.SetCellValue(content.ToString());
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(content.ToString(), out dateV);
string strtime = dateV.ToString("yyyy-MM-dd HH:mm:ss");
if (strtime.Substring(11, 8) == "00:00:00")
{
strtime = dateV.ToString("yyyy-MM-dd");
}
cell.SetCellValue(strtime);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(content.ToString(), out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
cell.SetCellValue(Convert.ToInt16(content));
break;
case "System.Int32":
cell.SetCellValue(Convert.ToInt32(content));
break;
case "System.Int64":
cell.SetCellValue(Convert.ToInt64(content));
break;
case "System.Byte":
cell.SetCellValue(Convert.ToInt32(content));
break;
case "System.Decimal": //浮点型
cell.SetCellValue(Convert.ToDouble(content));
break;
case "System.Double":
cell.SetCellValue(Convert.ToDouble(content));
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue(content.ToString());
break;
}
}
catch
{
cell.SetCellValue(content.ToString());
}
#endregion
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
byte[] b = ms.ToArray();
File.WriteAllBytes(fileName, b);
book = null;
ms.Close();
ms.Dispose();
}
protected void btnExport_Click(object sender, EventArgs e)
{
var list = DAL.WorkPlanRule.Get().Where(a => a.PublishTime.Year == Common.St.ToInt32(selYear.Value)).Select(a =>
{
int pid = 0, id = 0;
string pname = "", name = "";
GetIDAndName(a.Project, ref pid, ref id, ref pname, ref name);
return(new { ID_0 = pid, ID_1 = id, Name_0 = pname, Name_1 = name, M = a.PublishTime.Month, PublishTime = a.PublishTime, Name_3 = a.Project.Name });
}).GroupBy(a => a.ID_1).Select(a =>
{
return(new
{
PName = a.First().Name_0,
Name = a.First().Name_1,
M1 = a.Where(b => b.M == 1).Count(),
M2 = a.Where(b => b.M == 2).Count(),
M3 = a.Where(b => b.M == 3).Count(),
M4 = a.Where(b => b.M == 4).Count(),
M5 = a.Where(b => b.M == 5).Count(),
M6 = a.Where(b => b.M == 6).Count(),
M7 = a.Where(b => b.M == 7).Count(),
M8 = a.Where(b => b.M == 8).Count(),
M9 = a.Where(b => b.M == 9).Count(),
M10 = a.Where(b => b.M == 10).Count(),
M11 = a.Where(b => b.M == 11).Count(),
M12 = a.Where(b => b.M == 12).Count(),
S1 = string.Join("\n", a.Where(b => b.M == 1).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S2 = string.Join("\n", a.Where(b => b.M == 2).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S3 = string.Join("\n", a.Where(b => b.M == 3).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S4 = string.Join("\n", a.Where(b => b.M == 4).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S5 = string.Join("\n", a.Where(b => b.M == 5).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S6 = string.Join("\n", a.Where(b => b.M == 6).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S7 = string.Join("\n", a.Where(b => b.M == 7).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S8 = string.Join("\n", a.Where(b => b.M == 8).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S9 = string.Join("\n", a.Where(b => b.M == 9).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S10 = string.Join("\n", a.Where(b => b.M == 10).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S11 = string.Join("\n", a.Where(b => b.M == 11).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
S12 = string.Join("\n", a.Where(b => b.M == 12).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray())
});
}).OrderBy(a => a.PName).Where(a => (txtProjectParent.Value.Trim() == "" || a.PName.IndexOf(txtProjectParent.Value.Trim()) >= 0) && (txtProject.Value.Trim() == "" || a.Name.IndexOf(txtProject.Value.Trim()) >= 0));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_project.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("科教组项目");
// 内容
int i = 1;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.SetCellValue(o.PName);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
cell2.SetCellValue(o.M1);
NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3);
cell3.SetCellValue(o.M2);
NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4);
cell4.SetCellValue(o.M3);
NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5);
cell5.SetCellValue(o.M4);
NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6);
cell6.SetCellValue(o.M5);
NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7);
cell7.SetCellValue(o.M6);
NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8);
cell8.SetCellValue(o.M7);
NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9);
cell9.SetCellValue(o.M8);
NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10);
cell10.SetCellValue(o.M9);
NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11);
cell11.SetCellValue(o.M10);
NPOI.SS.UserModel.ICell cell12 = row2.CreateCell(12);
cell12.SetCellValue(o.M11);
NPOI.SS.UserModel.ICell cell13 = row2.CreateCell(13);
cell13.SetCellValue(o.M12);
i++;
}
NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(i);
row3.CreateCell(0).SetCellValue("总计");
row3.CreateCell(1).SetCellValue(list.Sum(a => a.M1 + a.M2 + a.M3 + a.M4 + a.M5 + a.M6 + a.M7 + a.M8 + a.M9 + a.M10 + a.M11 + a.M12));
row3.CreateCell(2).SetCellValue(list.Sum(a => a.M1));
row3.CreateCell(3).SetCellValue(list.Sum(a => a.M2));
row3.CreateCell(4).SetCellValue(list.Sum(a => a.M3));
row3.CreateCell(5).SetCellValue(list.Sum(a => a.M4));
row3.CreateCell(6).SetCellValue(list.Sum(a => a.M5));
row3.CreateCell(7).SetCellValue(list.Sum(a => a.M6));
row3.CreateCell(8).SetCellValue(list.Sum(a => a.M7));
row3.CreateCell(9).SetCellValue(list.Sum(a => a.M8));
row3.CreateCell(10).SetCellValue(list.Sum(a => a.M9));
row3.CreateCell(11).SetCellValue(list.Sum(a => a.M10));
row3.CreateCell(12).SetCellValue(list.Sum(a => a.M11));
row3.CreateCell(13).SetCellValue(list.Sum(a => a.M12));
NPOI.SS.UserModel.ISheet sheet2 = book.GetSheet("上线明细表");
// 内容
int j = 1;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet2.CreateRow(j);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.SetCellValue(o.S1);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
cell2.SetCellValue(o.S2);
NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3);
cell3.SetCellValue(o.S3);
NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4);
cell4.SetCellValue(o.S4);
NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5);
cell5.SetCellValue(o.S5);
NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6);
cell6.SetCellValue(o.S6);
NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7);
cell7.SetCellValue(o.S7);
NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8);
cell8.SetCellValue(o.S8);
NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9);
cell9.SetCellValue(o.S9);
NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10);
cell10.SetCellValue(o.S10);
NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11);
cell11.SetCellValue(o.S11);
NPOI.SS.UserModel.ICell cell12 = row2.CreateCell(12);
cell12.SetCellValue(o.S12);
j++;
}
// 写入到客户端
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(selYear.Value + "年项目上线频度表"), System.Text.Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
protected void btnExport_Click(object sender, EventArgs e)
{
var list = DAL.ProjectRule.Get().Where(a => txtSearch.Value == "" || a.Name.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0 || (a.Url.Length > 0 && a.Url.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0) || (a.TestUrl.Length > 0 && a.TestUrl.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("项目列表");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("名称");
NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("域名");
NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("是否显示");
NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("测试地址");
NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("网站文件名");
NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("数据库");
NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("测试用户名");
NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7);
cell7.SetCellValue("密码");
NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8);
cell8.SetCellValue("备注");
// 内容
int i = 1;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell00 = row2.CreateCell(0);
cell00.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell01 = row2.CreateCell(1);
cell01.SetCellValue(o.Url);
NPOI.SS.UserModel.ICell cell02 = row2.CreateCell(2);
cell02.SetCellValue(o.IsShow == 1?"是":"否");
NPOI.SS.UserModel.ICell cell03 = row2.CreateCell(3);
cell03.SetCellValue(o.TestUrl);
NPOI.SS.UserModel.ICell cell04 = row2.CreateCell(4);
cell04.SetCellValue(o.SiteFileName);
NPOI.SS.UserModel.ICell cell05 = row2.CreateCell(5);
cell05.SetCellValue(o.DatabaseName);
NPOI.SS.UserModel.ICell cell06 = row2.CreateCell(6);
cell06.SetCellValue(o.TestUserName);
NPOI.SS.UserModel.ICell cell07 = row2.CreateCell(7);
cell07.SetCellValue(o.TestPassword);
NPOI.SS.UserModel.ICell cell08 = row2.CreateCell(8);
cell08.SetCellValue(o.Remark);
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("项目表", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
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();
}
public void Object2Cell(System.Reflection.FieldInfo prop, object obj, NPOI.SS.UserModel.ICell cell)
#endif
{
//如果是一个公式计算结果的Cell，不能往里面生写
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
return;
}
var type = obj.GetType();
if (type == typeof(sbyte))
{
var value = (sbyte)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int16))
{
var value = (Int16)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int32))
{
var value = (Int32)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int64))
{
var value = (Int64)obj;
cell.SetCellValue(value);
return;
}
if (type == typeof(byte))
{
var value = (byte)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt16))
{
var value = (UInt16)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt32))
{
var value = (UInt32)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt64))
{
var value = (UInt64)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(double))
{
var value = (double)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(float))
{
var value = (float)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(string))
{
var value = (string)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(bool))
{
var value = (bool)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(DateTime))
{
var value = (DateTime)obj;
cell.SetCellValue(value);
return;
}
else if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
{
var argType = type.GenericTypeArguments[0];
//var atts = argType.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
//if (atts == null || atts.Length == 0)
// return;
//var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;
string sheetName = "LST_" + prop.Name;
int index = -1;
var link = cell.Hyperlink as HSSFHyperlink;
if (link != null && link.Address.Contains($"{sheetName}!A"))
{
var suffix = link.Address.Substring($"{sheetName}!A".Length);
index = System.Convert.ToInt32(suffix);
if (index > 0)
{
index--;
}
}
var sheet = this.GetSheetSure($"{sheetName}");
//var header = InitSheet(sheet, type);
var row = GetRowSure(sheet, index);
FillObjectList2Row(obj as System.Collections.IList, row, argType);
if (link == null)
{
link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
}
link.Address = $"{sheetName}!A{row.RowNum + 1}";
cell.SetCellValue(link.Address);
cell.Hyperlink = link;
var target = row.GetCell(0);
if (target != null)
{
var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex + 1}";
target.Hyperlink = linkBack;
target.SetCellValue(linkBack.Address);
}
}
else
{
var atts = type.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
if (atts == null || atts.Length == 0)
{
return;
}
var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;
var sheet = this.GetSheetSure(sheetAttr.SheetName);
var header = InitSheet(sheet, type);
string sheetName = sheetAttr.SheetName;
int index = -1;
var link = cell.Hyperlink as HSSFHyperlink;
if (link != null && link.Address.Contains($"{sheetName}!A"))
{
var suffix = link.Address.Substring($"{sheetName}!A".Length);
index = System.Convert.ToInt32(suffix);
if (index > 0)
{
index--;
}
}
var row = GetRowSure(sheet, index);
FillObject2Row(obj, row, header, type);
if (link == null)
{
link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
}
link.Address = $"{sheetName}!A{row.RowNum + 1}";
cell.SetCellValue(link.Address);
cell.Hyperlink = link;
var target = GetCellSure(row, 0, typeof(string));
if (target != null)
{
var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex+1}";
target.Hyperlink = linkBack;
target.SetCellValue(linkBack.Address);
}
}
}
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();
}
/// <summary>
/// 输出数据表格
/// </summary>
/// <param name="workbook">工作文档</param>
/// <param name="normalStyle">普通样式(用于表格内容)</param>
/// <param name="boldStyle">粗体样式(用于表格头部)</param>
/// <param name="table">表格数据</param>
public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table)
{
//创建Sheet页
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//行号
int rowIndex = 0;
//是否需要输出表头
bool isNeedCreateHeader = true;
//输出数据到Excel
foreach (DataRow rowData in table.Rows)
{
//忽略空数据行
if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count)
{
continue;
}
//列号
int colIndex = 0;
//Excel行
NPOI.SS.UserModel.IRow row = null;
//是否需要输入表头
if (isNeedCreateHeader)
{
isNeedCreateHeader = false;
//创建行
row = sheet.CreateRow(rowIndex);
//输出列名到Excel
colIndex = 0;
foreach (DataColumn kvp in table.Columns)
{
//列名
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = boldStyle;
//设置数据
cell.SetCellValue(kvp.ColumnName);
colIndex++;
}
rowIndex++;
}
//创建行
row = sheet.CreateRow(rowIndex);
//输出列值到Excel
colIndex = 0;
foreach (object val in rowData.ItemArray)
{
//列值
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = normalStyle;
//设置数据
//判断是否为空
if (val != null)
{
//不为空
//判断是否为RTF内容
if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name))
{
//RTF内容
cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val);
}
else
{
//文本内容
cell.SetCellValue(val.ToString());
}
}
else
{
//为空
cell.SetCellValue(string.Empty);
}
colIndex++;
}
rowIndex++;
}
//Excel列宽自动适应
if (table.Rows.Count >= 1 && sheet.GetRow(0) != null)
{
for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++)
{
sheet.AutoSizeColumn(k);
}
}
}
public void Export(Stream ms, DataTable dt, string postfix)
{
//var fileName = m_Handler.GetFileName();
NPOI.SS.UserModel.IWorkbook book = null;
if (postfix == ".xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (postfix == ".xlsx")
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
}
m_Handler.Encode(ref dt);
//WriteExcel(ref book, dt);
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
//// 写入
//MemoryStream ms = new MemoryStream();
book.Write(ms);
book = null;
//using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
//ms.Close();
//ms.Dispose();
}
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(strValue);
cell.CellStyle = style;
}
public static void CreateExcelColorFile(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
// 创建Workbook。
NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook();
if (fileInfo.Extension == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else if (fileInfo.Extension == ".xls")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
// 创建Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor");
// 设置列宽。
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
// 创建标题。
int rowIndex = 0;
NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0);
cell.SetCellValue("Excel颜色");
cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold));
// 空一行
rowIndex++;
// 创建标题行
rowIndex++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
cell = row.CreateCell(0);
cell.SetCellValue("索引");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("索引（NPOI）");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("名称");
cell.CellStyle = columnHeadCellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3));
cell = row.CreateCell(4);
cell.SetCellValue("十六进制代码");
cell.CellStyle = columnHeadCellStyle;
ExcelColor.KnownColors.ForEach(x =>
{
rowIndex++;
NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex);
contentRow.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0);
contentCell.SetCellValue(x.Index);
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(1);
contentCell.SetCellValue(x.IndexNPOI + "（NPOI）");
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(2);
contentCell.SetCellValue(x.Name);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(3);
contentCell.SetCellValue(x.Description);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(4);
contentCell.SetCellValue(x.HexString);
contentCell.CellStyle = contentCellStyle;
});
// 保存到文件。
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite);
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
}
/// <summary>
/// 设置单元格值，支持合并单元格
/// </summary>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCol"></param>
/// <param name="lastCol"></param>
/// <param name="value"></param>
/// <param name="sheet"></param>
public void SetCellValue(int firstRow, int lastRow, int firstCol, int lastCol, object value, NPOI.SS.UserModel.ISheet sheet)
{
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
for (int r = firstRow; r <= lastRow; r++)
{
row = sheet.GetRow(r);
if (row == null)
{
row = sheet.CreateRow(r);
}
for (int c = firstCol; c <= lastCol; c++)
{
cell = row.GetCell(c);
if (cell == null)
{
cell = row.CreateCell(c);
}
}
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
row = sheet.GetRow(firstRow);
cell = row.GetCell(firstCol);
if (value is bool)
{
cell.SetCellValue((bool)value);
}
if (value is string)
{
string svalue = (string)value;
if (svalue != null && svalue != "" && svalue.Substring(0, 1) == "=")
{
cell.SetCellFormula(svalue.Substring(1));
}
else
{
cell.SetCellValue((string)svalue);
}
}
if (value is NPOI.SS.UserModel.IRichTextString)
{
cell.SetCellValue((NPOI.SS.UserModel.IRichTextString)value);
}
if (value is DateTime)
{
if (this.DateTimeCellStyle != null)
{
cell.CellStyle = this.DateTimeCellStyle;
}
cell.SetCellValue((DateTime)value);
}
if (value is double)
{
cell.SetCellValue((double)value);
}
if (value is int)
{
cell.SetCellValue((int)value);
}
}