/// <summary> /// 从DataSet导出到MemoryStream流2003 /// </summary> /// <param>文件保存路径</param> /// <param>Excel文件中的Sheet名称</param> /// <param>存储数据的DataSet</param> /// <param>从哪一行开始写入，从0开始</param> /// <param>DataSet中的各列对应的数据类型</param> public static bool CreateExcel2003(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes) { try { if (startRow < 0) startRow = 0; var wb = new HSSFWorkbook(); var dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "pkm"; var si = PropertySetFactory.CreateSummaryInformation(); si.Title = si.Subject = "automatic genereted document"; si.Author = "pkm"; wb.DocumentSummaryInformation = dsi; wb.SummaryInformation = si; var sheet = wb.CreateSheet(sheetName); //sheet.SetColumnWidth(0, 50 * 256); //sheet.SetColumnWidth(1, 100 * 256); ICell cell; int j; var maxLength = 0; var curLength = 0; object columnValue; var dt = ds.Tables[0]; if (datatypes.Length < dt.Columns.Count) { datatypes = new NpoiDataType[dt.Columns.Count]; for (var i = 0; i < dt.Columns.Count; i++) { var dtcolumntype = dt.Columns[i].DataType.Name.ToLower(); switch (dtcolumntype) { case "string": datatypes[i] = NpoiDataType.String; break; case "datetime": datatypes[i] = NpoiDataType.Datetime; break; case "boolean": datatypes[i] = NpoiDataType.Bool; break; case "double": datatypes[i] = NpoiDataType.Numeric; break; default: datatypes[i] = NpoiDataType.String; break; } } } // 创建表头 var row = sheet.CreateRow(0); //样式 var style1 = wb.CreateCellStyle(); //字体 var font1 = wb.CreateFont(); //字体颜色 font1.Color = HSSFColor.White.Index; //字体加粗样式 font1.Boldweight = (short)FontBoldWeight.Bold; //style1.FillBackgroundColor = HSSFColor.WHITE.index; style1.FillForegroundColor = HSSFColor.Green.Index; //GetXLColour(wb, LevelOneColor);// 设置图案色 //GetXLColour(wb, LevelOneColor);// 设置背景色 style1.FillPattern = FillPattern.SolidForeground; //样式里的字体设置具体的字体样式 style1.SetFont(font1); //文字水平对齐方式 style1.Alignment = HorizontalAlignment.Center; //文字垂直对齐方式 style1.VerticalAlignment = VerticalAlignment.Center; row.HeightInPoints = 25; for (j = 0; j < dt.Columns.Count; j++) { columnValue = dt.Columns[j].ColumnName; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); var colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { //创建第0行的第j列 cell = row.CreateCell(j); //单元格式设置样式 cell.CellStyle = style1; try { cell.SetCellType(CellType.String); cell.SetCellValue(columnValue.ToString()); } catch (Exception ex) { throw new Exception(ex.Message); } } catch (Exception ex) { throw new Exception(ex.Message); } } // 创建每一行 for (var i = startRow; i < ds.Tables[0].Rows.Count; i++) { var dr = ds.Tables[0].Rows[i]; //创建第i行 row = sheet.CreateRow(i + 1); for (j = 0; j < dt.Columns.Count; j++) { columnValue = dr[j]; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); var colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { //创建第i行的第j列 cell = row.CreateCell(j); // 插入第j列的数据 try { var dtype = datatypes[j]; switch (dtype) { case NpoiDataType.String: { cell.SetCellType(CellType.Numeric); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Datetime: { cell.SetCellType(CellType.Numeric); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Numeric: { cell.SetCellType(CellType.Numeric); cell.SetCellValue(Convert.ToDouble(columnValue)); } break; case NpoiDataType.Bool: { cell.SetCellType(CellType.Numeric); cell.SetCellValue(Convert.ToBoolean(columnValue)); } break; case NpoiDataType.Richtext: { cell.SetCellType(CellType.Numeric); cell.SetCellValue(columnValue.ToString()); } break; } } catch (Exception ex) { cell.SetCellType(CellType.Numeric); cell.SetCellValue(columnValue.ToString()); throw new Exception(ex.Message); } } catch (Exception ex) { throw new Exception(ex.Message); } } } //生成文件在服务器上 using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write)) { wb.Write(fs); } return true; } catch (Exception er) { throw new Exception(er.Message); } }
6.从DataSet导出到MemoryStream流2007：
/// <summary> /// 从DataSet导出到MemoryStream流2007 /// </summary> /// <param>文件保存路径</param> /// <param>Excel文件中的Sheet名称</param> /// <param>存储数据的DataSet</param> /// <param>从哪一行开始写入，从0开始</param> /// <param>DataSet中的各列对应的数据类型</param> public static bool CreateExcel2007(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes) { try { if (startRow < 0) startRow = 0; var wb = new XSSFWorkbook(); var sheet = wb.CreateSheet(sheetName); ICell cell; int j; var maxLength = 0; int curLength; object columnValue; var dt = ds.Tables[0]; if (datatypes.Length < dt.Columns.Count) { datatypes = new NpoiDataType[dt.Columns.Count]; for (var i = 0; i < dt.Columns.Count; i++) { var dtcolumntype = dt.Columns[i].DataType.Name.ToLower(); switch (dtcolumntype) { case "string": datatypes[i] = NpoiDataType.String; break; case "datetime": datatypes[i] = NpoiDataType.Datetime; break; case "boolean": datatypes[i] = NpoiDataType.Bool; break; case "double": datatypes[i] = NpoiDataType.Numeric; break; default: datatypes[i] = NpoiDataType.String; break; } } } //创建表头 var row = sheet.CreateRow(0); //样式 var style1 = wb.CreateCellStyle(); //字体 var font1 = wb.CreateFont(); //字体颜色 font1.Color = HSSFColor.White.Index; //字体加粗样式 font1.Boldweight = (short)FontBoldWeight.Bold; //style1.FillBackgroundColor = HSSFColor.WHITE.index; //GetXLColour(wb, LevelOneColor); // 设置图案色 style1.FillForegroundColor = HSSFColor.Green.Index; //GetXLColour(wb, LevelOneColor);// 设置背景色 style1.FillPattern = FillPattern.SolidForeground; //样式里的字体设置具体的字体样式 style1.SetFont(font1); //文字水平对齐方式 style1.Alignment = HorizontalAlignment.Center; //文字垂直对齐方式 style1.VerticalAlignment = VerticalAlignment.Center; row.HeightInPoints = 25; for (j = 0; j < dt.Columns.Count; j++) { columnValue = dt.Columns[j].ColumnName; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); var colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { //创建第0行的第j列 cell = row.CreateCell(j); //单元格式设置样式 cell.CellStyle = style1; try { cell.SetCellValue(columnValue.ToString()); } catch (Exception ex) { throw new Exception(ex.Message); } } catch (Exception ex) { throw new Exception(ex.Message); } } // 创建每一行 for (var i = startRow; i < ds.Tables[0].Rows.Count; i++) { var dr = ds.Tables[0].Rows[i]; //创建第i行 row = sheet.CreateRow(i + 1); for (j = 0; j < dt.Columns.Count; j++) { columnValue = dr[j]; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); var colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { //创建第i行的第j列 cell = row.CreateCell(j); // 插入第j列的数据 try { var dtype = datatypes[j]; switch (dtype) { case NpoiDataType.String: { cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Datetime: { cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Numeric: { cell.SetCellValue(Convert.ToDouble(columnValue)); } break; case NpoiDataType.Bool: { cell.SetCellValue(Convert.ToBoolean(columnValue)); } break; case NpoiDataType.Richtext: { cell.SetCellValue(columnValue.ToString()); } break; } } catch (Exception ex) { cell.SetCellValue(columnValue.ToString()); throw new Exception(ex.Message); } } catch (Exception ex) { throw new Exception(ex.Message); } } } //生成文件在服务器上 using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write)) { wb.Write(fs); } return true; } catch (Exception er) { throw new Exception(er.Message); } }
7.读Excel-根据NpoiDataType创建的DataTable列的数据类型：