protected void Unnamed2_Click(object sender, EventArgs e)
{
List <ModelStu> list = StuDaTa.GetData();//集合数据
string SheetName = "请假学生表";
string[] arr = "编号,年龄,姓名,PC".Split(',');
string DocName = SheetName + DateTime.Now.ToString("yyyyMMdd_HHmmss");
#region XlsDocument对象
var doc = new AppLibrary.WriteExcel.XlsDocument();
#region 解决文件名火狐导出乱码问题
doc.FileName = Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox", StringComparison.Ordinal) != -1 ? string.Format("\"{0}\"", DocName) : System.Web.HttpUtility.UrlPathEncode(DocName);
#endregion
#endregion
var g1 = list.GroupBy(s => s.age);
foreach (var item1 in g1)
{
SheetName = "KEY值：" + item1.Key;
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
cells.Add(1, 1, SheetName).HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
sheet.Cells.Merge(1, 1, 1, arr.Length);
for (int m = 0; m < arr.Length; m++)
{
cells.Add(2, m + 1, arr[m].ToString());
}
List <ModelStu> list2 = new List <ModelStu>();
foreach (var itemtmp in item1)
{
list2.Add(itemtmp);
} //新集合
var g2 = list2.GroupBy(s => s.name);
var rowIndex = 1; //applibrary的row和col索引从1开始
foreach (var item2 in g2)
{
List <ModelStu> list3 = new List <ModelStu>();
foreach (var itemtmp in item2)
{
list3.Add(itemtmp);
} //新集合
cells.Add(rowIndex++, 1, item2.Key).HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
foreach (var item3 in list3)
{
var colIndex = 1;
cells.Add(rowIndex, colIndex++, item3.id);
cells.Add(rowIndex, colIndex++, item3.age);
cells.Add(rowIndex, colIndex++, item3.name);
cells.Add(rowIndex, colIndex++, item3.pc);
rowIndex++;
}
sheet.Cells.Merge(rowIndex, rowIndex, 1, arr.Length);
}
}
doc.Send();
Response.Flush();
Response.End();
//return File(DocName, "application/ms-excel");
}
/// <summary>
/// 普通单表导出npoi（集合数据+样式）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
string filename = "test.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称");
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.PINK.index;
var row0 = sheet1.CreateRow(0).CreateCell(0);
row0.SetCellValue("This is a Sample");//sheet标题
row0.CellStyle = style;
var j = 17;
#region 居中/自动换行
CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式
styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式
styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式
styleCenter.WrapText = true; //自动换行
sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter;
var cell17 = sheet1.CreateRow(j).CreateCell(j);
var cell172 = sheet1.CreateRow(j).CreateCell(j + 1);
cell17.CellStyle = styleCenter;
cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
//cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
j++;
#endregion
#region 设置宽高度
sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。
var rowwh = sheet1.CreateRow(j);
rowwh.HeightInPoints = 50; //高度
rowwh.CreateCell(j).SetCellValue("宽高度");
j++;
#endregion
#region 自适应宽度（对中文不友好）+自动换行
/*场景：
* 12林学1班
* 12林学1班
*/
CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式
autoAndWrap.WrapText = true; //自动换行
var rowwhauto = sheet1.CreateRow(j);
var cellauto = rowwhauto.CreateCell(j);
cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度");
sheet1.AutoSizeColumn(j);
cellauto.CellStyle = autoAndWrap;
j++;
#endregion
#region 设置背景色
CellStyle style1 = hssfworkbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
sheet1.CreateRow(j).CreateCell(j).CellStyle = style1;
j++;
#endregion
#region 自定义背景色
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228);
HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228);
CellStyle style2 = hssfworkbook.CreateCellStyle();
style2.FillPattern = FillPatternType.SOLID_FOREGROUND;
style2.FillForegroundColor = hssFColor.GetIndex();
sheet1.CreateRow(j).CreateCell(j).CellStyle = style2;
j++;
#endregion
#region 设置字体颜色
CellStyle style3 = hssfworkbook.CreateCellStyle();
Font font1 = hssfworkbook.CreateFont();
font1.Color = hssFColor.GetIndex();//颜色
style3.SetFont(font1);
var cell20 = sheet1.CreateRow(j).CreateCell(j);
cell20.CellStyle = style3;
cell20.SetCellValue("666666666");
j++;
#endregion
List <ModelStu> data = StuDaTa.GetData();
string[] arrthead = { "ID", "name", "age", "pc" };
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1));
Row row1 = sheet1.CreateRow(1);
for (int i = 0; i < arrthead.Length; i++)
{
row1.CreateCell(i).SetCellValue(arrthead[i]);
}
for (int i = 0; i < data.Count; i++)
{
Row row = sheet1.CreateRow(i + 2);
var colIndex = 0;
row.CreateCell(colIndex++).SetCellValue(data[i].id);
row.CreateCell(colIndex++).SetCellValue(data[i].name);
row.CreateCell(colIndex++).SetCellValue(data[i].age);
row.CreateCell(colIndex).SetCellValue(data[i].pc);
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
Response.BinaryWrite(file.GetBuffer());
Response.End();
hssfworkbook = null;
file.Close();
file.Dispose();
}
