/// <summary>
/// 生成导会员卡信息的excel文件，并返回导出的url地址
/// </summary>
/// <param name="entities">会员卡信息集合</param>
/// <returns>导出excel的url</returns>
public string Export <T>(IList <T> entities, string sheetName = "导出数据")
{
SetPropertyInfo(typeof(T));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
_book = book;
ISheet sheet = book.CreateSheet(sheetName);
if (OnSheetCreated != null)
{
OnSheetCreated(sheet);
}
FillHead(sheet);
for (var i = 0; i < entities.Count(); i++)
{
FillRow(entities[i], sheet, i + 1);
}
using (var ms = new MemoryStream())
{
book.Write(ms);
var fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
using (var fs = new FileStream(ExportTempPath(fileName), FileMode.Create))
{
book.Write(fs);
}
return(ExportTempUrl(fileName));
}
}
/// <summary>
/// Saves all data created to the file.
/// </summary>
/// <param name="fileName">Name of the file.</param>
public void SaveFile(string fileName)
{
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
//Saving all data in the physical excel file
FileStream excelFile = new FileStream(fileName, FileMode.Create);
_workbook.Write(excelFile);
excelFile.Close();
}
static void WriteToFile()
{
//Write the stream data of workbook to the root directory
System.IO.FileStream file = new System.IO.FileStream(@"test.xls", System.IO.FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public ActionResult CreateExcel()
{
HSSFWorkbook wk = new HSSFWorkbook();
ISheet tb = wk.CreateSheet("MyFirst");
//创建一行，此行为第二行:创建行是从0开始
IRow row = tb.CreateRow(1);
ICell cell;
for (int i = 0; i < 20; i++)
{
cell = row.CreateCell(i); //在第二行中创建单元格
cell.SetCellValue("This is a test");//循环往第二行的单元格中添加数据
}
// ISheet tb = wk.CreateSheet("MyFirst");
// HSSFPatriarch patr = sheet.CreateDrawingPatriarch();
// HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
// comment1.String = new HSSFRichTextString("Hello World");
// comment1.Author = "NPOI Team"
//;
// cell.CellComment = comment1;
FileStream fs = new FileStream(@"f:/test.xls", FileMode.Create);
wk.Write(fs);
fs.Close();
return View();
}
public void NpoiExcel(DataTable dt, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 导出WOrd文件
/// </summary>
public void Generate()
{
//首先在内存中创建一个Excel文件
var fileStream = new FileStream(outputPath, FileMode.OpenOrCreate);
var workbook = new HSSFWorkbook();
//创建一个表(sheet)--并获取他
var sheet = (HSSFSheet) workbook.CreateSheet("华中科技大学工程");
//首先把需要多少个cell创建好----有多少个数据就有多少行--每行有3个数据
for (var i = 0; i <= dataList.Count; i++)
{
sheet.CreateRow(i);
}
//创建表头
GenerateHeaderRow(workbook, sheet);
//然后给每行---创建单元格---添加数据
GenerateDataRow(workbook, sheet);
try
{
//获取目标文件流--写入文件数据
workbook.Write(fileStream);
fileStream.Close();
}
catch (Exception e)
{
Console.Error.WriteLine(e.StackTrace);
MessageBox.Show("文件导出失败", "失败");
return;
}
finally
{
if (fileStream != null)
fileStream.Close();
}
MessageBox.Show("Excel文件导出成功", "完成");
}
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
for (int i = 0; i < sourceDs.Tables.Count; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceDs.Tables[i].TableName);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return ms;
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("项目库组织代码");
row1.CreateCell(1).SetCellValue("老G6H组织代码");
Dictionary <string, object> dicwhere = new Dictionary <string, object>();
new CreateCriteria(dicwhere)
.Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
IList <QtOrgDygxModel> qtOrgDygxes = QtOrgDygxService.Find(dicwhere).Data;//数据库的所有数据
for (int i = 0; i < qtOrgDygxes.Count; i++)
{
QtOrgDygxModel model = qtOrgDygxes[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.Xmorg);
row.CreateCell(1).SetCellValue(model.Oldorg);
}
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();
}
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).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++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
public static void WriteExcel(List <DataTable> dts, string filePath)
{
try
{
if (!string.IsNullOrEmpty(filePath)) // && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");
createColumnName(book, sheet);
createCells(book, sheet, dts[0], 0, 1);
createCells(book, sheet, dts[1], 3, 4);
createCells(book, sheet, dts[2], 6, 7);
createCells(book, sheet, dts[3], 9, 10);
createCells(book, sheet, dts[4], 12, 14);
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
{
MemoryStream ms = new MemoryStream();
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < headers.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(headers[i]);
}
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public override void Write(Stream outStream)
{
Workbook workbook = new HSSFWorkbook();
IEnumerable<User> users = User.SelectAll();
foreach (User user in users)
{
IEnumerable<TimeBlock> timeBlocks = TimeBlock.SelectByUserAndDateRange(user.UserId, FromDate, ToDate);
if (timeBlocks.Count() > 0)
{
Sheet sheet = workbook.CreateSheet(user.Name);
Row header = sheet.CreateRow(0);
header.CreateCell(0).SetCellValue("Resource");
header.CreateCell(1).SetCellValue("Date");
header.CreateCell(2).SetCellValue("Client");
header.CreateCell(3).SetCellValue("Work Package");
header.CreateCell(4).SetCellValue("Hours");
int i = 1;
foreach (TimeBlock timeBlock in timeBlocks)
{
Row row = sheet.CreateRow(i ++);
row.CreateCell(0).SetCellValue(timeBlock.User.Name);
row.CreateCell(1).SetCellValue(timeBlock.Date.ToString("dd/MM/yy"));
row.CreateCell(2).SetCellValue(timeBlock.Job.Client.Name);
row.CreateCell(3).SetCellValue(timeBlock.JobId);
row.CreateCell(4).SetCellValue(Convert.ToDouble(timeBlock.Time));
}
}
}
workbook.Write(outStream);
}
static void Main(string[] args)
{
//Excel worksheet combine example
//You will be prompted to select two Excel files. test.xls will be created that combines the sheets
//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel document (*.xls)|*.xls";
ofd.Title = "Select first Excel document";
if (ofd.ShowDialog() == DialogResult.OK)
{
HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
ofd.Title = "Select second Excel document";
if (ofd.ShowDialog() == DialogResult.OK)
{
HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
HSSFWorkbook product = new HSSFWorkbook();
for (int i = 0; i < book1.NumberOfSheets; i++)
{
HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
sheet1.CopyTo(product, sheet1.SheetName, true, true);
}
for (int j = 0; j < book2.NumberOfSheets; j++)
{
HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
sheet2.CopyTo(product, sheet2.SheetName, true, true);
}
product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
}
}
}
public static MemoryStream ExportExcel(DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
//string ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("代码");
row1.CreateCell(1).SetCellValue("名称");
//row1.CreateCell(2).SetCellValue("备注");
Dictionary <string, object> dicWhere = new Dictionary <string, object>();
new CreateCriteria(dicWhere)
.Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
IList <ProcurementCatalogModel> procurementCatalogs = ProcurementCatalogService.Find(dicWhere).Data;
for (int i = 0; i < procurementCatalogs.Count; i++)
{
ProcurementCatalogModel model = procurementCatalogs[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.FCode);
row.CreateCell(1).SetCellValue(model.FName);
//row.CreateCell(2).SetCellValue(model.FRemark);
}
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();
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("科目代码");
row1.CreateCell(1).SetCellValue("科目名称");
row1.CreateCell(2).SetCellValue("科目类别");
IList <BudgetAccountsModel> budgetAccounts = BudgetAccountsService.ExportData();
//List<BudgetAccountsModel> models = budgetAccounts.ToList();
for (int i = 0; i < budgetAccounts.Count; i++)
{
BudgetAccountsModel model = budgetAccounts[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.KMDM);
row.CreateCell(1).SetCellValue(model.KMMC);
row.CreateCell(2).SetCellValue(model.KMLB == "0" ? "收入" : "支出");
}
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();
}
public void NpoiExcel(DataTable dt, string title, string s)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
string[] ss = s.Split(',');
for (int i = 0; i < ss.Length; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(ss[i]);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
//double a = 0;
//string b = "";
//try
//{
// a = Convert.ToDouble(dt.Rows[I][j].ToString());
// row2.CreateCell(j).SetCellValue(a);
// sheet.SetColumnWidth(j, 20 * 150);
//}
//catch
//{
// b = dt.Rows[I][j].ToString();
// row2.CreateCell(j).SetCellValue(b);
// sheet.SetColumnWidth(j, 20 * 150);
//}
string DgvValue = dt.Rows[I][j].ToString();
if (j == 8 || j == 9)
{
row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
}
else
{
row2.CreateCell(j).SetCellValue(DgvValue);
}
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
//数据导出为Excel
private void btnExport_Click(object sender, EventArgs e)
{
string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("customers");
int rowIndex = 0;//手动处理行索引
while (reader.Read())
{
int autoId = reader.GetInt32(0);
string name = reader.GetString(1);
string cellPhone = reader.GetString(2);
//数据为空,那么就不能Get..,所以要在这里判断
string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空
DateTime BuyDate = reader.GetDateTime(4);
string carNum = reader.GetString(5);
string bracketNum = reader.GetString(6);
IRow row = sheet.CreateRow(rowIndex);
rowIndex++;
row.CreateCell(0).SetCellValue(autoId);
row.CreateCell(1).SetCellValue(name);
row.CreateCell(2).SetCellValue(cellPhone);
//如果为空或Null,把Excel这个单元格设置为值为空
if (string.IsNullOrEmpty(landLine))
{
row.CreateCell(3).SetCellType(CellType.BLANK);
}
else
{
row.CreateCell(3).SetCellValue(landLine);
}
//============设置日期格式=======
//创建单元格样式对象
ICellStyle cellStyle = wb.CreateCellStyle();
//设置单元格样式对象属性
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
ICell BuyDateRow = row.CreateCell(4);
BuyDateRow.CellStyle = cellStyle;
BuyDateRow.SetCellValue(BuyDate);
//==============设置完成========
row.CreateCell(5).SetCellValue(carNum);
row.CreateCell(6).SetCellValue(bracketNum);
}
using (FileStream fs = File.OpenWrite("Customers.xls"))
{
wb.Write(fs);
MessageBox.Show("ok");
}
}
}
}
/// <summary>
/// 下载当前最新配置
/// </summary>
/// <returns></returns>
public ActionResult DownLoadFile()
{
var sourceList = _iswcmgr.GetAllSearchWord();
//创建Excel文件的对象
var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
var sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
var row1 = sheet1.CreateRow(0);
var fileName = "最新配置-" + DateTime.Now.ToString("yyyyMMdd") + "-" + new Random().Next(999) + ".xls";
row1.CreateCell(0).SetCellValue("关键词");
row1.CreateCell(1).SetCellValue("同义词");
row1.CreateCell(2).SetCellValue("是否删除");
for (var i = 0; i < sourceList.Count; i++)
{
var rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(sourceList[i].TargetWord);
rowtemp.CreateCell(1).SetCellValue(sourceList[i].SourceWord);
rowtemp.CreateCell(2).SetCellValue("1");
}
// 写入到客户端
byte[] file;
using (var ms = new System.IO.MemoryStream())
{
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
file = ms.ToArray();
}
return(File(file, "application/vnd.ms-excel", fileName));
}
private Stream RenderDataSetToExcel(DataSet ds) {
MemoryStream ms = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet;
HSSFRow headerRow;
//for (int k = ds.Tables.Count - 1, len = 0; len <= k; k--) {
// DataTable dt = ds.Tables[k];
foreach(DataTable dt in ds.Tables) {
sheet = (HSSFSheet)workbook.CreateSheet(dt.TableName);
headerRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
int rowIndex = 1;
foreach (DataRow row in dt.Rows) {
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns) {
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
public static void Main(String[] args)
{
WriteJavaDoc();
HSSFWorkbook wb = new HSSFWorkbook();
SheetWriter sw = new SheetWriter(wb);
NumberToTextConversionExamples.ExampleConversion[] exampleValues = NumberToTextConversionExamples.GetExampleConversions();
for (int i = 0; i < exampleValues.Length; i++)
{
TestCases.SS.Util.NumberToTextConversionExamples.ExampleConversion example = exampleValues[i];
sw.AddTestRow(example.RawDoubleBits, example.ExcelRendering);
}
MemoryStream baos = new MemoryStream();
wb.Write(baos);
byte[] fileContent = baos.ToArray();
ReplaceNaNs(fileContent, sw.GetReplacementNaNs());
FileInfo outputFile = new FileInfo("ExcelNumberRendering.xls");
FileStream os = File.OpenWrite(outputFile.FullName);
os.Write(fileContent, 0, fileContent.Length);
os.Close();
Console.WriteLine("Finished writing '" + outputFile.FullName + "'");
}
private static bool addRecord(float data)
{
try
{
//close the FileStream before specifying any changes in the HSSFSheet object
FileStream fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("Arkusz1");
fs.Close();
DateTime current = DateTime.Now;
DateTime thisDay = DateTime.Today;
Row row = sheet.CreateRow(sheet.LastRowNum + 1);
row.CreateCell(0).SetCellValue(data);
row.CreateCell(1).SetCellValue(DateTime.Now.ToShortTimeString());
row.CreateCell(2).SetCellValue(thisDay.ToString("d"));
//reinitialize data before having the workbook object write to the Stream
sheet.ForceFormulaRecalculation = true;
fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
templateWorkbook.Write(fs);
fs.Close();
return true;
}
catch
{
return false;
}
}
public static string ExportEasy(string[] heads, List<string> data)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//填充表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < heads.Length; i++)
{
dataRow.CreateCell(i).SetCellValue(heads[i]);
}
//填充内容
for (int i = 0; i < data.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
string[] str = data[i].Split('|');
for (int j = 0; j < heads.Length; j++)
{
dataRow.CreateCell(j).SetCellValue(str[j]);
}
}
//保存
using (MemoryStream ms = new MemoryStream())
{
var name = @"C:\tempfile\" + Guid.NewGuid().ToString() + ".xls";
try
{
using (FileStream fs2 = new FileStream(name, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs2);
}
#region 下载
//以字符流的形式下载文件
FileStream fs = new FileStream(name, FileMode.Open);
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
HttpContext.Current.Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("report.xls", System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(bytes);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
#endregion
return "下载成功";
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
File.Delete(name);
}
}
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
string ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("账号");
row1.CreateCell(1).SetCellValue("密码");
row1.CreateCell(2).SetCellValue("姓名");
Dictionary <string, object> dicWhere = new Dictionary <string, object>();
new CreateCriteria(dicWhere).Add(ORMRestrictions <string> .Eq("ProductBZ", ProductBZ));
IList <QTProductUserModel> qTProductUsers = QTProductUserService.ServiceHelper.LoadWithPageInfinity("GQT.QT.ALLProductUsers", dicWhere).Results;
//List<BudgetAccountsModel> models = budgetAccounts.ToList();
for (int i = 0; i < qTProductUsers.Count; i++)
{
QTProductUserModel model = qTProductUsers[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.ProductUserCode);
row.CreateCell(1).SetCellValue(model.ProductUserPwd);
row.CreateCell(2).SetCellValue(model.ProductUserName);
}
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();
}
/// <summary>
/// 服务商结算订单导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
{
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);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;//流位置归零
return(ms);
//写入到客户端
}
public void TestFail()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("Simple.xls");
POIFSFileSystem fs = new POIFSFileSystem(is1);
HSSFWorkbook wb = new HSSFWorkbook(fs);
//set POIFS properties after constructing HSSFWorkbook
//(a piece of code that used to work up to POI 3.0.2)
SummaryInformation summary1 = (SummaryInformation)PropertySetFactory.Create(fs.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
summary1.Title=(title);
//Write the modified property back to POIFS
fs.Root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME).Delete();
fs.CreateDocument(summary1.ToStream(), SummaryInformation.DEFAULT_STREAM_NAME);
//save the workbook and read the property
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
out1.Close();
POIFSFileSystem fs2 = new POIFSFileSystem(new MemoryStream(out1.ToArray()));
SummaryInformation summary2 = (SummaryInformation)PropertySetFactory.Create(fs2.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
//Assert.Failing assertion
Assert.AreEqual(title, summary2.Title);
}
public void TestWrite()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsA);
POIFSFileSystem fs = new POIFSFileSystem(is1);
// Open the workbook, not preserving nodes
HSSFWorkbook wb = new HSSFWorkbook(fs);
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
// Check now
MemoryStream in1 = new MemoryStream(out1.ToArray());
POIFSFileSystem fs2 = new POIFSFileSystem(in1);
// Check that we have the new entries
fs2.Root.GetEntry("Workbook");
try
{
fs2.Root.GetEntry("WORKBOOK");
Assert.Fail();
}
catch (FileNotFoundException) { }
// And it can be Opened
HSSFWorkbook wb2 = new HSSFWorkbook(fs2);
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream ExportMemory(DataTable source, List <string> caption)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssfff");
string fileName = "查询结果" + dateTime + ".xls";
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
public FileResult Generate(SearchEntity searchEntity)
{
int num;
var arrayData = this.GetArrayData(searchEntity, out num);
var str = "根基对账单";
using (var stream = System.IO.File.OpenRead(base.Server.MapPath(string.Format("~/Excel/Market/{0}.xls", str))))
{
var xlBook = new HSSFWorkbook(stream);
this.GenerateM1(arrayData, xlBook);
this.GenerateM2(arrayData, xlBook);
this.GenerateM3(arrayData, xlBook);
if (xlBook.NumberOfSheets > 3)
{
xlBook.RemoveSheetAt(0);
xlBook.RemoveSheetAt(0);
xlBook.RemoveSheetAt(0);
xlBook.FirstVisibleTab = 0;
}
var str3 = string.Format("{0}{1:yyMMddHHmmss}.xls", str, DateTime.Now);
var path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
if (System.IO.File.Exists(path))
{
str3 = str3 + Guid.NewGuid().ToString() + ".xls";
path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
}
using (var stream2 = System.IO.File.OpenWrite(path))
{
xlBook.Write(stream2);
}
return this.File(path, "application/vnd.ms-excel", str3);
}
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
if (caption == null)
{
caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
}
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
/// <summary>
/// Excel导出返回文件路径
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
/// <param name="FilePath"></param>
/// <param name="ext"></param>
/// <returns></returns>
public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
{
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);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
}
FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));
book.Write(fs2);
fs2.Close();
return(FilePath);
//写入到客户端
}
/*
* 响应到客户端
*
* Param fileName 文件名
*/
public static void WriteToClient(String fileName, HSSFWorkbook workbook)
{
//Write the stream data of workbook to the root directory
//FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Expires = 0;
HttpContext.Current.Response.CacheControl = "no-cache";
HttpContext.Current.Response.ContentType = "application/x-excel";
//inline
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
workbook.Write(HttpContext.Current.Response.OutputStream);
Sheet sheet = workbook.GetSheetAt(0);
sheet = null;
workbook.Dispose();
workbook = null;
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
//file.Close();
}
public static Boolean GerarArquivo_Excel(DataSet dados, String path, String fileName, String template, String[] planilhas)
{
Boolean sucesso = false;
FileStream file = null;
FileStream fileTemplate = null;
HSSFWorkbook xlPackage = null;
try
{
fileTemplate = new FileStream(path + template, FileMode.Open, FileAccess.Read);
xlPackage = new HSSFWorkbook(fileTemplate);
Int32 index = 0;
foreach (String planilha in planilhas)
{
PreencherDados_Excel(xlPackage, dados.Tables[index], planilha);
index++;
}
file = new FileStream(path + fileName, FileMode.Create);
xlPackage.Write(file);
file.Close();
sucesso = true;
}
catch (Exception ex)
{
throw ex;
}
return sucesso;
}
private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return file;
}
MemoryStream GenerateData()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
var movies = (from m in db.Movies
select m).ToList();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow headerRow = sheet1.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("ID");
headerRow.CreateCell(1).SetCellValue("电影名");
headerRow.CreateCell(2).SetCellValue("发布日期");
headerRow.CreateCell(3).SetCellValue("类型");
headerRow.CreateCell(4).SetCellValue("价格");
for (int i = 0; i < movies.Count; i++)
{
IRow Row = sheet1.CreateRow(i + 1);
Row.CreateCell(0).SetCellValue(movies[i].ID);
Row.CreateCell(1).SetCellValue(movies[i].Title);
Row.CreateCell(2).SetCellValue(movies[i].ReleaseDate);
Row.CreateCell(3).SetCellValue(movies[i].Genre);
Row.CreateCell(4).SetCellValue(movies[i].Price.ToString());
}
hssfworkbook.Write(ms);
return ms;
}
public void SaveFile(string path, List<string> text)
{
var wb = new HSSFWorkbook();
var sheet = wb.CreateSheet("Dictionary");
int rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("Word");
row.CreateCell(1).SetCellValue("Count");
rowIndex++;
foreach(var str in text)
{
var first = str.Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries)[0];
var second = str.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)[1];
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(first);
row.CreateCell(1).SetCellValue(second);
rowIndex++;
}
using(var fileStr = new FileStream(path, FileMode.Create))
{
wb.Write(fileStr);
}
}
public byte[] GetExcelBufferFromDataTable(DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("Sheet1");
sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i - 1][dt.Columns[j].ColumnName].ToString());
}
}
MemoryStream memstream = new MemoryStream();
try
{
hssfworkbook.Write(memstream);
byte[] buffer = memstream.GetBuffer();
return buffer;
}
finally
{
memstream.Close();
}
}
public static MemoryStream getExcel(DataTable dt, HttpContextBase content)
{
var book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
//写入到客户端
var ms = new MemoryStream();
book.Write(ms);
book = null;
ms.Close();
ms.Dispose();
return ms;
}
static void Main(string[] args)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
hssfworkbook.CreateSheet("Sheet3");
hssfworkbook.CreateSheet("Sheet4");
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public static MemoryStream CreateExcel2003StreamMulti(List <ExcelDataWapperEntity> dataentity, bool inOneSheet)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
int tick = 1;
if (inOneSheet)
{
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
for (int i = 0; i < dataentity.Count; i++)
{
int datastartrow = 0;
if (i > 0)
{
datastartrow = dataentity[i - 1].DataSource.Rows.Count + (string.IsNullOrEmpty(dataentity[i - 1].DataSourceDesc) ? 2 : 3);
}
WriteData(ref sheet1, dataentity[i].DataSource, dataentity[i].ColumnInfo, dataentity[i].DataSourceDesc, datastartrow);
}
}
else
{
foreach (ExcelDataWapperEntity w in dataentity)
{
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(w.DataSourceTitle ?? string.Format("Sheet{0}", tick));
WriteData(ref sheet1, w.DataSource, w.ColumnInfo, w.DataSourceDesc);
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
return(ms);
}
//excel导出，最多只能导出65535条记录，没有考虑分sheet页，后续如果数据量较多再扩充
public static void writeExcel(DataTable dt, ExcelInfo ei, String[] colTitle, int[] colWidth)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ei.sheetName);
HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
for (int j = 0; j < colTitle.Length;j++)
{
//设置列宽
sheet.SetColumnWidth(j, colWidth[j] * 255);
rowtitle.CreateCell(j).SetCellValue(colTitle[j]);
}
int i = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow newrow = (HSSFRow)sheet.CreateRow(i);
int col = 0;
for (; col < dt.Columns.Count; col++)
newrow.CreateCell(col).SetCellValue(Convert.ToString(row[col]));
i++;
}
try
{
using (Stream stream = File.Open(ei.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 批量导出本校第一批派位学生
/// </summary>
/// <returns></returns>
public FileResult ExportStu2()
{
// schoolname = "401";
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
List <Staff> listRainInfo = db.Staffs.ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("电脑号");
row1.CreateCell(1).SetCellValue("姓名");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
}
public void NpoiExcel(System.Data.DataTable dt, string file)
{
try
{
double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
int sheetCount = (int)(Math.Ceiling(sheetCountdbl));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
for (int c = 0; c < sheetCount; c++)
{
string sheetname = "Sheet" + (c + 1).ToString();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
int js = 0;//计数
//表内容
for (int i = c * 60000; i < dt.Rows.Count; i++)
{
if (js > 59999)
{
break;
}
IRow row = sheet.CreateRow(js + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = style;
cell.SetCellValue(dt.Rows[i][j].ToString());
}
js++;
}
}
FileStream fs = File.Create(file);
book.Write(fs);
fs.Flush();
fs.Close();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
}
public void ProcessRequest(HttpContext context)
{
string projectName = context.Request["pname"].Trim();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet projectSheet = workbook.CreateSheet("项目各报销项汇总");
HSSFSheet projectReimSummarySheet = workbook.CreateSheet("项目报销项金额汇总");
HSSFSheet projectSummarySheet = workbook.CreateSheet("项目报销总金额汇总");
List<ProjectCategory> queryCategoryList = new List<ProjectCategory>();
DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem();
List<ReimItem> reimItemList;
if (!string.IsNullOrWhiteSpace(projectName))
{
reimItemList = dalReimItem.GetReimItems(projectName.Trim(), 0, 0);
}
else
{
reimItemList = dalReimItem.GetAllDistinctReimItems();
}
List<ReimItem> reimItemSummaryList = dalReimItem.GetReimItemSummaryValues(projectName, 0, 0, null);
List<ReimItem> projectReimSummaryList = dalReimItem.GetProjectReimItems(projectName, 0);
SetProjectReimSheet(projectSheet, reimItemList);
SetProjectReimSummarySheet(projectReimSummarySheet, reimItemSummaryList);
SetProjectSummarySheet(projectSummarySheet, projectReimSummaryList);
string fileName = "项目报销项汇总";
System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create);
workbook.Write(file);
file.Dispose();
////插入值
FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls"));
context.Response.Clear();
context.Response.ClearHeaders();
context.Response.Buffer = false;
Encoding code = Encoding.GetEncoding("gb2312");
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.HeaderEncoding = code;//这句很重要
context.Response.ContentType = "application/octet-stream";
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
context.Response.WriteFile(DownloadFile.FullName);
if (File.Exists(context.Server.MapPath(fileName + ".xls")))
{
File.Delete(context.Server.MapPath(fileName + ".xls"));
}
context.Response.Flush();
}
public static void ExportFile(DataSet ds, string filepath)
{
if(string.IsNullOrEmpty(filepath))
{
return;
}
if(ds == null || ds.Tables.Count == 0)
{
return;
}
try
{
IWorkbook hssfworkbook = new HSSFWorkbook();
using (FileStream fs = File.Create(filepath))//path=mmm.xls;
{
foreach (DataTable table in ds.Tables)
{
ISheet sheet = hssfworkbook.CreateSheet(table.TableName);
//head
IRow firstRow = sheet.CreateRow(0);
int cellIndex = 0;
foreach (DataColumn col in table.Columns)
{
cellIndex = InsertCell(firstRow, cellIndex, col.ColumnName);
}
//rows
int rowIndex = 1;
foreach (DataRow dr in table.Rows)
{
cellIndex = 0;
IRow row = sheet.CreateRow(rowIndex);
for (int i = 0; i < table.Columns.Count; i++)
{
string val = "";
if(dr[i] is byte[])
{
val = (dr[i] as byte[]).ToHexString();
}
else
{
val = dr[i].ToString();
}
cellIndex = InsertCell(row, cellIndex, val);
}
rowIndex++;
}
}
hssfworkbook.Write(fs);//保存文件
}
}
catch (Exception)
{
//LogHelper
}
}
/**
* Writes a spReadsheet to a <c>MemoryStream</c> and Reads it back
* from a <c>ByteArrayStream</c>.<p/>
* Useful for verifying that the serialisation round trip
*/
public static HSSFWorkbook WriteOutAndReadBack(HSSFWorkbook original)
{
using (MemoryStream baos = new MemoryStream(4096))
{
original.Write(baos);
return new HSSFWorkbook(baos);
}
}
public FileResult ExportExcel(HSSFWorkbook book, string fileName)
{
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", string.Format("{0}.xls", fileName));
}
/// <summary>
/// 把DataTable的数据写入Stream.
/// </summary>
/// <param name="stream"></param>
/// <param name="dt"></param>
public static void Write(Stream stream, DataTable dt)
{
HSSFWorkbook book = new HSSFWorkbook();
string sheetName = dt.TableName.IsNotEmpty() ? dt.TableName : "sheet1";
ISheet sheet = book.CreateSheet(sheetName);
Write(sheet, dt);
book.Write(stream);
}
private void btnOutput_Click(object sender, EventArgs e)
{
List <MODEL.Classes> lists = cm.GetAllClass(false);
/*
* 1.创建出工作薄
* 2.为这个工作薄创建出工作表
* 3.为这个表创建出行
* 4.为这个行创建出每一列(单元格cell)--添加数据
* 5.文件的写入
*/
//创建一个工作薄
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
//创建一张工作表
ISheet sheet1 = workbook.CreateSheet("sh1");
// workbook.CreateSheet("Sh1");
///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1");
////workbook.CreateSheet("sh1"); // workbook.CreateSheet("sh1");
//需要导出的数据在集合中，每一个对象对应着后期表的一行数据
for (int i = 0; i < lists.Count; i++)
{
//创建行
IRow row = sheet1.CreateRow(i);
//创建第一个单元格
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(lists[i].CID);
//创建第2个单元格
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(lists[i].CName);
//创建第3个单元格
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(lists[i].CCount);
//创建第4个单元格
ICell cell4 = row.CreateCell(3);
cell4.SetCellValue(lists[i].CImg);
//创建第5个单元格
ICell cell5 = row.CreateCell(4);
cell5.SetCellValue(lists[i].CIsDel);
//创建第6个单元格
ICell cell6 = row.CreateCell(5);
//cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日")); //日期值会被当成double
cell6.SetCellValue(lists[i].CAddTime);
//如何修改日期类型的格式
ICellStyle cs = workbook.CreateCellStyle();
IDataFormat df = workbook.CreateDataFormat();
cs.DataFormat = df.GetFormat("yyyy年MM月dd日");
cell6.CellStyle = cs;
}
using (FileStream fs = new FileStream("aa.xls", FileMode.Create))
{
workbook.Write(fs);
MessageBox.Show("ok");
}
}
public override void ExecuteResult(ControllerContext context)
{
var fs = new FileStream(context.HttpContext.Server.MapPath(
@"\Content\UpdatePeople.xls"), FileMode.Open, FileAccess.Read);
var wb = new HSSFWorkbook(fs, true);
var sheet = wb.GetSheet("Sheet1");
var r = 1;
foreach (var p in UpdatePeopleRows())
{
var row = sheet.CreateRow(r++);
var c = 0;
row.CreateCell(c++).SetCellValue(p.PeopleId);
row.CreateCell(c++).SetCellValue(p.Title);
row.CreateCell(c++).SetCellValue(p.First);
row.CreateCell(c++).SetCellValue(p.GoesBy);
row.CreateCell(c++).SetCellValue(p.Last);
row.CreateCell(c++).SetCellValue(p.Suffix);
row.CreateCell(c++).SetCellValue(p.Email1);
row.CreateCell(c++).SetCellValue(p.Email2);
row.CreateCell(c++).SetCellValue(p.Gender);
if (p.BirthDate.HasValue)
row.CreateCell(c++).SetCellValue(p.BirthDate.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
if (p.Anniversary.HasValue)
row.CreateCell(c++).SetCellValue(p.Anniversary.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
if (p.Joined.HasValue)
row.CreateCell(c++).SetCellValue(p.Joined.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
row.CreateCell(c++).SetCellValue(p.Cell);
row.CreateCell(c++).SetCellValue(p.Work);
row.CreateCell(c++).SetCellValue(p.Member);
if (p.Grade.HasValue)
row.CreateCell(c++).SetCellValue(p.Grade.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
row.CreateCell(c++).SetCellValue(p.Marital);
row.CreateCell(c++).SetCellValue(p.FamilyPos);
row.CreateCell(c++).SetCellValue(p.AltName);
row.CreateCell(c++).SetCellValue(p.Campus);
row.CreateCell(c++).SetCellValue(p.School);
row.CreateCell(c++).SetCellValue(p.Occupation);
row.CreateCell(c++).SetCellValue(p.Employer);
if (p.Deceased.HasValue)
row.CreateCell(c++).SetCellValue(p.Deceased.Value);
else
row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
}
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=UpdatePeople.xls");
Response.Charset = "";
wb.Write(Response.OutputStream);
}
public void Test48832()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Starts empty
Assert.IsNull(wb.DocumentSummaryInformation);
Assert.IsNull(wb.SummaryInformation);
// Add new properties
wb.CreateInformationProperties();
Assert.IsNotNull(wb.DocumentSummaryInformation);
Assert.IsNotNull(wb.SummaryInformation);
// Set Initial values
wb.SummaryInformation.Author = (/*setter*/"Apache POI");
wb.SummaryInformation.Keywords = (/*setter*/"Testing POI");
wb.SummaryInformation.CreateDateTime = DateUtil.GetJavaDate(12345);
wb.DocumentSummaryInformation.Company = (/*setter*/"Apache");
// Save and reload
MemoryStream baos = new MemoryStream();
wb.Write(baos);
MemoryStream bais =
new MemoryStream(baos.ToArray());
wb = new HSSFWorkbook(bais);
// Ensure Changes were taken
Assert.IsNotNull(wb.DocumentSummaryInformation);
Assert.IsNotNull(wb.SummaryInformation);
Assert.AreEqual("Apache POI", wb.SummaryInformation.Author);
Assert.AreEqual("Testing POI", wb.SummaryInformation.Keywords);
Assert.AreEqual(12345, DateUtil.GetExcelDate(wb.SummaryInformation.CreateDateTime.Value));
Assert.AreEqual("Apache", wb.DocumentSummaryInformation.Company);
// Set some more, save + reload
wb.SummaryInformation.Comments = (/*setter*/"Resaved");
baos = new MemoryStream();
wb.Write(baos);
bais = new MemoryStream(baos.ToArray());
wb = new HSSFWorkbook(bais);
// Check again
Assert.IsNotNull(wb.DocumentSummaryInformation);
Assert.IsNotNull(wb.SummaryInformation);
Assert.AreEqual("Apache POI", wb.SummaryInformation.Author);
Assert.AreEqual("Testing POI", wb.SummaryInformation.Keywords);
Assert.AreEqual("Resaved", wb.SummaryInformation.Comments);
Assert.AreEqual(12345, DateUtil.GetExcelDate(wb.SummaryInformation.CreateDateTime.Value));
Assert.AreEqual("Apache", wb.DocumentSummaryInformation.Company);
}
public FileResult ShunFengExcel()
{
try
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ExamUserInfoModel models = new ExamUserInfoModel();
models.GetUserInfo(null);
//获取list数据
var tlst = models.ListUserInfo;
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("工号");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("入职日期");
row1.CreateCell(3).SetCellValue("职等");
row1.CreateCell(4).SetCellValue("职位");
row1.CreateCell(5).SetCellValue("本职等对应技能等级");
row1.CreateCell(6).SetCellValue("已经考取技能等级");
row1.CreateCell(7).SetCellValue("最近一次理论考试成绩");
row1.CreateCell(8).SetCellValue("最近一次通过理论时间");
row1.CreateCell(9).SetCellValue("最近一次实践成绩");
row1.CreateCell(10).SetCellValue("最近一次实践考核通过时间");
row1.CreateCell(11).SetCellValue("最高可考技能等级");
row1.CreateCell(12).SetCellValue("可申请技能等级");
row1.CreateCell(13).SetCellValue("最近一次绩效成绩要求");
//将数据逐步写入sheet1各个行
for (int i = 0; i < tlst.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode); //工号
rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName); //姓名
rowtemp.CreateCell(2).SetCellValue(tlst[i].EntryDate.ToString()); //入职日期
rowtemp.CreateCell(3).SetCellValue(tlst[i].RankName); //职等
rowtemp.CreateCell(4).SetCellValue(tlst[i].PostName); //职位
rowtemp.CreateCell(5).SetCellValue(tlst[i].SkillLevel); //本职等对应技能等级
rowtemp.CreateCell(6).SetCellValue(tlst[i].CurrentSkillLevel); //已经考取技能等级
rowtemp.CreateCell(7).SetCellValue(tlst[i].ExamScore); //最近一次理论考试成绩
rowtemp.CreateCell(8).SetCellValue(tlst[i].LastExamTime.ToString()); //最近一次通过理论时间
rowtemp.CreateCell(9).SetCellValue(tlst[i].TheoreticalAchievement.ToString()); //最近一次实践成绩
rowtemp.CreateCell(10).SetCellValue(tlst[i].PracticeTime.ToString()); //最近一次实践考核通过时间
rowtemp.CreateCell(11).SetCellValue(tlst[i].HighestTestSkill); //最高可考技能等级
rowtemp.CreateCell(12).SetCellValue(tlst[i].ApplicationLevel); //可申请技能等级
rowtemp.CreateCell(13).SetCellValue(tlst[i].Achievement); //最近一次绩效成绩要求
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "人员信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
catch (Exception)
{
throw;
}
}
public void ProcessRequest(HttpContext context)
{
CommonFunction.CheckUser();
context.Response.CacheControl = "no-cache";
context.Response.ContentType = "text/plain";
string term = context.Request["term"];
string searchKey = context.Request["searchKey"].Trim();
string teacherType = context.Request["teacherType"].Trim();
DalOperationNorm dalnorm = new DalOperationNorm();
DataSet ds = dalnorm.getTeacherLoad(term, searchKey, teacherType);
HSSFWorkbook workbook = new HSSFWorkbook();
for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
{
HSSFSheet sheet = workbook.CreateSheet(ds.Tables[tableIndex].TableName);
DataRowCollection drc = ds.Tables[tableIndex].Rows;
for (int i = 0; i < ds.Tables[tableIndex].Columns.Count; i++)
{
sheet.CreateRow(0).CreateCell(i).SetCellValue(ds.Tables[tableIndex].Columns[i].ColumnName);
}
for (int i = 0; i < drc.Count; i++)
{
for (int j = 0; j < ds.Tables[tableIndex].Columns.Count; j++)
{
sheet.CreateRow(i + 1).CreateCell(j).SetCellValue(drc[i][j].ToString());
}
}
}
string fileName = "norm";
System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create);
workbook.Write(file);
file.Dispose();
////插入值
FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls"));
context.Response.Clear();
context.Response.ClearHeaders();
context.Response.Buffer = false;
Encoding code = Encoding.GetEncoding("gb2312");
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.HeaderEncoding = code;//这句很重要
context.Response.ContentType = "application/octet-stream";
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
context.Response.WriteFile(DownloadFile.FullName);
if (File.Exists(context.Server.MapPath(fileName + ".xls")))
{
File.Delete(context.Server.MapPath(fileName + ".xls"));
}
context.Response.Flush();
}
public FileResult OmitCheckCountExportExcel()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("未巡统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("应巡");
row.CreateCell(2).SetCellValue("已巡");
row.CreateCell(3).SetCellValue("未巡");
row.CreateCell(4).SetCellValue("完成率");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 2);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.PointCount);
row.CreateCell(2).SetCellValue(v.PointCount0);
row.CreateCell(3).SetCellValue(v.PointCount1);
row.CreateCell(4).SetCellValue(v.PointCount2);
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult Excel2()
{
MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext();
List <DataEliminate> list = new List <DataEliminate>();
if (Session["solvelist"] != null)
{
//将session转为集合
list = (List <DataEliminate>)Session["solvelist"];
}
NPOI.HSSF.UserModel.HSSFWorkbook Book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = Book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("任务编号");
row1.CreateCell(1).SetCellValue("任务名称");
row1.CreateCell(2).SetCellValue("线路编号");
row1.CreateCell(3).SetCellValue("线路编号");
row1.CreateCell(4).SetCellValue("有无故障");
row1.CreateCell(5).SetCellValue("缺陷级别");
row1.CreateCell(6).SetCellValue("缺陷类型");
row1.CreateCell(7).SetCellValue("消缺时间");
row1.CreateCell(8).SetCellValue("发现时间");
row1.CreateCell(9).SetCellValue("缺陷描述");
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode);
rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName);
rowtemp.CreateCell(2).SetCellValue(list[i].lineCode);
if (list[i].isBug == 1)
{
rowtemp.CreateCell(3).SetCellValue("有");
}
else
{
rowtemp.CreateCell(3).SetCellValue("无");
}
rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName);
rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString());
rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString());
rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
Book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssff");
string fileName = "查询结果" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
/// <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();
}
//导出 dt,和表头都传
public Stream ExportExcel(DataTable dt, List <EColName> list)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//组建表头
int index = 0;
foreach (var i in list)
{
row1.CreateCell(index).SetCellValue(i.col);
i.sortseq = index;
index = index + 1;
}
//将数据逐步写入sheet1各个行
double d0 = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < list.Count; jj++)
{
string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
string coldata = dt.Rows[i][colname].ToString();
string dbtype = dt.Columns[colname].DataType.ToString();
if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
{
if (double.TryParse(coldata, out d0))
{
d0 = Convert.ToDouble(coldata);
}
rowtemp.CreateCell(jj).SetCellValue(d0);
}
else
{
rowtemp.CreateCell(jj).SetCellValue(coldata);
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(ms);
}
public static MemoryStream CreateExcel2003Stream(DataTable dt, Dictionary <string, string> columnInfo)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
WriteData(ref sheet1, dt, columnInfo, string.Empty);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
return(ms);
}
//导出 只传dt
public Stream ExportExcel(DataTable dt)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//组建表头
DataColumnCollection dcc = dt.Columns;
int index = 0;
foreach (DataColumn i in dcc)
{
row1.CreateCell(index).SetCellValue(i.ColumnName);
index = index + 1;
}
double d0 = 0;
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < dcc.Count; jj++)
{
string dbtype = dcc[jj].DataType.ToString();
string coldata = dt.Rows[i][dcc[jj].ColumnName].ToString();
if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
{
if (double.TryParse(coldata, out d0))
{
d0 = Convert.ToDouble(coldata);
}
rowtemp.CreateCell(jj).SetCellValue(d0);
}
else
{
rowtemp.CreateCell(jj).SetCellValue(coldata);
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(ms);
}
private void ExcelExport()
{
string sheet1Title = "Sheet1";
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(sheet1Title);
NPOI.SS.UserModel.IRow headerRow4Sheet1 = sheet1.CreateRow(0);
ExcelCellStyle cellStyle = new ExcelCellStyle(book);
ICell cell;//初始化ICell准备设值
#region 设定Sheet1的头行
cell = headerRow4Sheet1.CreateCell(0); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户ID");
cell = headerRow4Sheet1.CreateCell(1); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户名");
cell = headerRow4Sheet1.CreateCell(2); cell.CellStyle = cellStyle.style; cell.SetCellValue("客户地址");
#endregion
/*0行已经设为表头行，内容从第1行开始设值*/
int sheet1RowID = 1;
Customer[] listCustomer = client.GetCustomer("");
foreach (Customer customer in listCustomer)
{
IRow r = sheet1.CreateRow(sheet1RowID);
cell = r.CreateCell(0); cell.SetCellValue(customer.customerID); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(1); cell.SetCellValue(customer.customerName); cell.CellStyle = cellStyle.style;
cell = r.CreateCell(2); cell.SetCellValue(customer.customerAddress); cell.CellStyle = cellStyle.style;
sheet1RowID += 1;//每做一个循环就到下一行设置
}
/*以下是做自适应宽度*/
ChangeStyle(book, sheet1);
/*最后作输出流导出文件*/
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("客户列表信息", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}