ASP.NET导出导入Excel
1.用Spire.XLS.dll和Spire.License.dll导入和导出Excel
View Code
1 private void ExportExcelFile() 2 { 3 //创建一个流 4 using (MemoryStream stream = new MemoryStream()) 5 { 6 //创建Excel文档 7 Spire.Xls.Workbook book = new Spire.Xls.Workbook(); 8 //创建工作簿 9 Spire.Xls.Worksheet sheet = book.Worksheets[0]; 10 sheet.Name = "导出的文档"; 11 sheet[1, 1].Text = "学籍主号"; 12 sheet[1, 1].ColumnWidth = 15; 13 //加粗 14 sheet[1, 1].Style.Font.IsBold = true; 15 sheet[1, 1].Style.VerticalAlignment = VerticalAlignType.Center; 16 sheet[1, 1].Style.HorizontalAlignment = HorizontalAlignType.Center; 17 sheet[1, 1].Style.Font.Color = System.Drawing.Color.Red;//字体变红 18 19 sheet[1, 2].Text = "年级"; 20 sheet[1, 2].ColumnWidth = 15; 21 sheet[1, 2].Style.Font.IsBold = true; 22 sheet[1, 2].Style.VerticalAlignment = VerticalAlignType.Center; 23 sheet[1, 2].Style.HorizontalAlignment = HorizontalAlignType.Center; 24 sheet[1, 2].Style.Color = System.Drawing.Color.Red;//背景变红 25 26 sheet[2, 1].Text = "A10001"; 27 sheet[2, 1].ColumnWidth = 15; 28 sheet[2, 1].Style.Font.IsBold = true; 29 sheet[2, 1].Style.VerticalAlignment = VerticalAlignType.Center; 30 sheet[2, 1].Style.HorizontalAlignment = HorizontalAlignType.Center; 31 sheet[2, 1].BorderAround(LineStyleType.Thin);//边框样式 32 33 sheet[3, 1].Text = "张三"; 34 sheet[3, 1].ColumnWidth = 15; 35 sheet[3, 1].Style.Font.IsBold = true; 36 sheet[3, 1].Style.VerticalAlignment = VerticalAlignType.Center; 37 sheet[3, 1].Style.HorizontalAlignment = HorizontalAlignType.Center; 38 39 sheet[5, 5].Text = "12121"; 40 sheet[5, 5].ColumnWidth = 15; 41 sheet[5, 5].Style.Font.IsBold = true; 42 sheet[5, 5].Style.VerticalAlignment = VerticalAlignType.Center; 43 sheet[5, 5].Style.HorizontalAlignment = HorizontalAlignType.Center; 44 sheet[5, 5].Merge(sheet[10, 10]);//合并单元格好像有错误！ 45 46 book.SaveToStream(stream); 47 Response.AddHeader("Content-Disposition", ("attachment;filename=" + "12.xls")); 48 Response.Charset = "UTF-8"; 49 Response.ContentType = "application/json"; 50 Response.BinaryWrite(stream.ToArray()); 51 stream.Close(); 52 stream.Dispose(); 53 } 54 } 55 private void ImportExcelFile() 56 { 57 using (Stream stream = File.OpenRead(@"C:\Users\Administrator\Downloads\12.xls")) 58 { 59 //创建Excel文档 60 Spire.Xls.Workbook book = new Spire.Xls.Workbook(); 61 //加载 工作簿 62 book.LoadFromStream(stream); 63 if (book.Worksheets.Count > 0) 64 { 65 Spire.Xls.Worksheet sheet = book.Worksheets[0]; 66 for (int i = 1; i <= sheet.LastRow; i++) 67 { 68 Response.Write("【" + i + "】"); 69 for (int j = 1; j <= sheet.LastColumn; j++) 70 { 71 Response.Write("【" + j + "】"); 72 string value = sheet[i, j].Value; 73 Response.Write(value); 74 } 75 Response.Write("<br/>"); 76 } 77 } 78 } 79 }
导出结果
2.用NPOI导入和导出Excel
View Code
1 private void ExportExcel() 2 { 3 HSSFWorkbook hssfworkbook = new HSSFWorkbook();//创建Excel 4 ISheet sheet1 = hssfworkbook.CreateSheet("我的第一页");//创建工作簿 5 IRow row = sheet1.CreateRow(10); //创建一行 6 ICell cell = row.CreateCell(10); //创建一列 7 cell.SetCellValue("导出的值");//设置值 8 //创建单元格颜色 9 ICellStyle style = hssfworkbook.CreateCellStyle(); 10 //边框样式 11 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; 12 style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; 13 style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; 14 style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; 15 //居中 16 style.Alignment = HorizontalAlignment.CENTER; 17 style.VerticalAlignment = VerticalAlignment.CENTER; 18 //背景颜色 19 style.FillForegroundColor = 11; 20 style.FillPattern = FillPatternType.SOLID_FOREGROUND; 21 IFont font = hssfworkbook.CreateFont();//创建字体颜色 22 font.FontHeight = 20 * 20;//字体大小 23 font.Color = 60;//字体颜色 24 style.SetFont(font); //设置到style中 25 cell.CellStyle = style;//将颜色赋值给单元格 26 sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //合并单元格 27 IRow rows = sheet1.CreateRow(2); 28 rows.CreateCell(2, CellType.STRING).SetCellValue("张三"); 29 30 //绘制分隔线 31 using (MemoryStream stream = new MemoryStream()) 32 { 33 hssfworkbook.Write(stream); 34 stream.Flush(); 35 stream.Position = 0; 36 Response.AddHeader("Content-Disposition", ("attachment;filename=" + "12.xls")); 37 Response.Charset = "UTF-8"; 38 Response.ContentType = "application/json"; 39 Response.BinaryWrite(stream.ToArray()); 40 stream.Close(); 41 stream.Dispose(); 42 } 43 } 44 private void ImportExcel() 45 { 46 using (Stream stream = File.OpenRead(@"C:\Users\Administrator\Downloads\12.xls")) 47 { 48 IWorkbook workbook = new HSSFWorkbook(stream);//从流内容创建Workbook对象 49 if (workbook.NumberOfSheets > 0) 50 { 51 ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作表 52 for (int i = 0; i <= sheet.LastRowNum; i++) 53 { 54 Response.Write("【" + (i + 1) + "】"); 55 if (sheet.GetRow(i) != null) 56 { 57 for (int j = 0; j <= sheet.GetRow(i).LastCellNum; j++) 58 { 59 Response.Write("【" + j + "】"); 60 if (sheet.GetRow(i).GetCell(j) != null) 61 { 62 string value = sheet.GetRow(i).GetCell(j).ToString(); 63 Response.Write(value); 64 } 65 } 66 } 67 Response.Write("<br/>"); 68 } 69 } 70 } 71 }
导出结果
3.用MyXls导入和导出Excel
View Code
1 private void ExportExcelMultipleHeaders() 2 { 3 using (MemoryStream stream = new MemoryStream()) 4 { 5 XlsDocument xls = new XlsDocument(); 6 string fileName = "汇总统计"; 7 xls.FileName = fileName; 8 org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.Add(fileName); 9 10 ColumnInfo Column = new ColumnInfo(xls, sheet); 11 Column.Width = 150 * 100;//单元格的宽度 12 Column.ColumnIndexStart = 0;//索引从0开始 13 Column.ColumnIndexEnd = 2; 14 sheet.AddColumnInfo(Column); 15 16 Cells cell = new Cells(sheet);//新建一个工作簿 17 18 XF headStyle = xls.NewXF();//样式 19 headStyle.Font.FontName = "宋体"; 20 headStyle.Font.Bold = true;//加粗 21 headStyle.Font.Height = 150 * 5;//字体的大小 22 headStyle.Font.Color = org.in2bits.MyXls.Colors.Red;//字体颜色 23 headStyle.VerticalAlignment = VerticalAlignments.Centered; 24 headStyle.HorizontalAlignment = HorizontalAlignments.Centered; 25 cell.Add(1, 1, "编号", headStyle); 26 cell.Add(1, 2, "名称", headStyle); 27 cell.Add(1, 3, "规格", headStyle); 28 cell.Add(1, 4, "单位", headStyle); 29 30 XF head = xls.NewXF(); 31 head.VerticalAlignment = VerticalAlignments.Centered; 32 head.HorizontalAlignment = HorizontalAlignments.Centered; 33 head.BottomLineStyle = 40;//边框的样式 34 cell.Add(2, 1, "1", head); 35 cell.Add(2, 2, "2", head); 36 cell.Add(2, 3, "3", head); 37 cell.Add(2, 4, "4", head); 38 39 cell.Add(3, 1, "11", head); 40 cell.Add(3, 2, "22", head); 41 cell.Add(3, 3, "33", head); 42 cell.Add(3, 4, "44", head); 43 44 MergeArea area1 = new MergeArea(4, 5, 3, 5);//合并单元格 注意：它是按行和列的最大值和最小值来合并的 45 sheet.AddMergeArea(area1); 46 //cell.Add(5, 4, "sdjflsdj", head);//这个数据是显示不了的 47 cell.Add(4, 3, "sdjflsdj", head);//要想填充合并单元格后的数据一定要是他的合并单元格开始的位置 48 49 xls.Save(stream); 50 Response.AddHeader("Content-Disposition", ("attachment;filename=" + "12.xls")); 51 Response.Charset = "UTF-8"; 52 Response.ContentType = "application/json"; 53 Response.BinaryWrite(stream.ToArray()); 54 stream.Close(); 55 stream.Dispose(); 56 } 57 } 58 private void ImportExcelMultipleHeaders() 59 { 60 using (Stream stream = File.OpenRead(@"C:\Users\Administrator\Downloads\12.xls")) 61 { 62 XlsDocument xls = new XlsDocument(stream); 63 string fileName = "汇总统计"; 64 xls.FileName = fileName; 65 if (xls.Workbook.Worksheets.Count() > 0) 66 { 67 org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets[0]; 68 for (int i = 1; i <= sheet.Rows.MaxRow; i++) 69 { 70 Response.Write("【" + i + "】"); 71 for (int j = 1; j <= sheet.Rows[(ushort)i].MaxCellCol; j++) 72 { 73 Response.Write("【" + j + "】"); 74 try 75 { 76 if (sheet.Rows[(ushort)i].GetCell((ushort)j).Value != null) 77 { 78 string value = sheet.Rows[(ushort)i].GetCell((ushort)j).Value.ToString(); 79 Response.Write(value); 80 } 81 } 82 catch { } 83 } 84 Response.Write("<br/>"); 85 } 86 } 87 stream.Close(); 88 stream.Dispose(); 89 } 90 }
导出结果
