public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("Date Range");
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
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 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>
/// 导出数据
/// </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;
}
}
}
private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
{
SetCellRangeAddress(sheet, 0, 0, 0, 1);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
SetCellRangeAddress(sheet, 0, 0, 6, 7);
SetCellRangeAddress(sheet, 0, 0, 9, 10);
SetCellRangeAddress(sheet, 0, 0, 12, 14);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("输入功率标定");
row.CreateCell(3).SetCellValue("输出功率标定");
row.CreateCell(6).SetCellValue("反射功率标定");
row.CreateCell(9).SetCellValue("ALC功率标定");
row.CreateCell(12).SetCellValue("衰减补偿");
row.GetCell(0).CellStyle = GetCellStyle(book);
row.GetCell(3).CellStyle = GetCellStyle(book);
row.GetCell(6).CellStyle = GetCellStyle(book);
row.GetCell(9).CellStyle = GetCellStyle(book);
row.GetCell(12).CellStyle = GetCellStyle(book);
row = sheet.CreateRow(1);
for (int i = 0; i < 4; i++)
{
row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
row.GetCell(3 * i).CellStyle = GetCellStyle(book);
row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
}
row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
row.GetCell(12).CellStyle = GetCellStyle(book);
row.GetCell(13).CellStyle = GetCellStyle(book);
row.GetCell(14).CellStyle = GetCellStyle(book);
}
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;
}
}
/// <summary>
/// 加边框
/// </summary>
/// <param Name="rowindex">1开始</param>
/// <param Name="cellIndex">1开始</param>
public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
{
ICellStyle styel = workbook.CreateCellStyle();
styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 11;
font1.Boldweight = 600;
font1.FontName = "宋体";
styel.SetFont(font1);
for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
{
for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
{
sheet.GetRow(rowindex).RowStyle = styel;
ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );
HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;
Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
Style.DataFormat = 0;
Style.SetFont(font1);
cell.CellStyle = Style;
}
}
}
//将一个集合中的数据写入到Excel
private void btnInclude_Click(object sender, EventArgs e)
{
//对象初始化器
List<Person> list = new List<Person>(){
new Person(){Name="路人甲",Age=18,Mail="*****@*****.**"},
new Person(){Name="路人乙",Age=28,Mail="*****@*****.**"},
new Person(){Name="路人丙",Age=38,Mail="*****@*****.**"},
};
//创建工作簿
IWorkbook wb = new HSSFWorkbook();
//创建工作表
ISheet sheet = wb.CreateSheet("测试");
//创建行
for (int i = 0; i < list.Count();i++ )
{
IRow row=sheet.CreateRow(i);
//创建单元格
row.CreateCell(0).SetCellValue(list[i].Name);
row.CreateCell(1).SetCellValue(list[i].Age);
row.CreateCell(2).SetCellValue(list[i].Mail);
}
using (FileStream fs=File.OpenWrite("NewTestExcel.xls"))
{
wb.Write(fs);
}
MessageBox.Show("Ok");
}
/// <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>
/// <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();
}
/// <summary>
/// 获取多个isheet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <ISheet> GetSheet(string filePath)
{
List <ISheet> sheet1 = new List <ISheet>();
using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
{
NPOI.XSSF.UserModel.XSSFWorkbook workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
for (int i = 0; i < workBook.NumberOfSheets; i++)
{
sheet1.Add(workBook.GetSheetAt(i));
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
for (int i = 0; i < workBook.NumberOfSheets; i++)
{
sheet1.Add(workBook.GetSheetAt(i));
}
}
}
return(sheet1);
}
public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> 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);
//row1.RowStyle.FillBackgroundColor = "";
int i = 0;
foreach (var item in title)
{
row1.CreateCell(i).SetCellValue(item);
i++;
}
//将数据逐步写入sheet1各个行
for (int j = 0; j < list.Count; j++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
rowtemp.CreateCell(1).SetCellValue(list[j].userName);
rowtemp.CreateCell(2).SetCellValue(list[j].phone);
}
return(book);
}
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 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);
}
/// <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>
/// 下载当前最新配置
/// </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));
}
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
dataRow[j] = row.GetCell(j).ToString();
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
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;
}
/// <summary>
/// 导出Excel
/// 将 DataTable 内容导出到Excel文件中
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="filePath">文件路径</param>
/// <param name="dataSetName">默认为：DataSet1</param>
/// <returns>导出结果</returns>
public static bool DataSet2Excel(DataSet ds, string filePath, ExcelWriterConfig config = null)
{
bool isSucceed = false;
IWorkbook iWorkbook = null;
if (filePath.EndsWith("xlsx"))
{
iWorkbook = new XSSFWorkbook();
}
else if (filePath.EndsWith("xls"))
{
iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
int sheetCount = 1;
foreach (DataTable dt in ds.Tables)
{
string sheetName = string.Format("Sheet{0}", sheetCount.ToString());
if (config != null && config.SheetNameList != null && config.SheetNameList.Count >= sheetCount)
{
sheetName = config.SheetNameList[sheetCount - 1];
}
sheetCount = sheetCount + 1;
ISheet workSheet = iWorkbook.CreateSheet(sheetName);
//表头
IRow headerRow = workSheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow dataRow = workSheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
using (FileStream fOut = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite)) // 写入流
{
fOut.Flush();
iWorkbook.Write(fOut);//写入文件
isSucceed = true;
}
iWorkbook = null;
return(isSucceed);
}
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue(Title);
foreach (var org in list)
{
if (org.Value == true)
{
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
cell.SetCellValue(org.Key);
}
}
}
public static Workbook CreateWorkBookWithSheet()
{
Workbook workbook = new HSSFWorkbook();
workbook.CreateSheet();
return workbook;
}
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);
}
/**
* Makes sure that a formula referring to the named range parses properly
*/
private static void ConfirmParseFormula(HSSFWorkbook workbook)
{
Ptg[] ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
}
public CMixExcel(Stream s)
{
if (IsUpperVer2003)
_excelPackage = new ExcelPackage(s);
else
_HSSFWorkbook = new HSSFWorkbook(s);
}
public void TestWriteSheetSimple() {
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
for (int rownum = 0; rownum < 100; rownum++) {
r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2) {
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
wb.Write(out1);
out1.Close();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2003ToString(string path)
{
HSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
string dr = "";
if (cell == null) { dr = ""; }
else { dr = cell.ToString(); }
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
sb.Append("</table>");
return sb.ToString();
}
/// <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 TestAvg()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(1,B2:B3)");
ICell a4 = sh.CreateRow(4).CreateCell(1);
a4.SetCellValue(1);
ICell a5 = sh.CreateRow(5).CreateCell(1);
a5.SetCellValue(7);
ICell a6 = sh.CreateRow(6).CreateCell(1);
a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2");
ICell a7 = sh.CreateRow(7).CreateCell(1);
a7.CellFormula = ("SUBTOTAL(1,B2:B7)");
fe.EvaluateAll();
Assert.AreEqual(2.0, a3.NumericCellValue);
Assert.AreEqual(8.0, a6.NumericCellValue);
Assert.AreEqual(3.0, a7.NumericCellValue);
}
public void TestRecord()
{
POIFSFileSystem fs = new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("WithFormattedGraphTitle.xls"));
// Check we can Open the file via usermodel
HSSFWorkbook hssf = new HSSFWorkbook(fs);
// Now process it through eventusermodel, and
// look out for the title records
ChartTitleFormatRecordGrabber grabber = new ChartTitleFormatRecordGrabber();
Stream din = fs.CreateDocumentInputStream("Workbook");
HSSFRequest req = new HSSFRequest();
req.AddListenerForAllRecords(grabber);
HSSFEventFactory factory = new HSSFEventFactory();
factory.ProcessEvents(req, din);
din.Close();
// Should've found one
Assert.AreEqual(1, grabber.chartTitleFormatRecords.Count);
// And it should be of something interesting
AlRunsRecord r =
(AlRunsRecord)grabber.chartTitleFormatRecords[0];
Assert.AreEqual(3, r.GetFormatCount());
}
public void TestGetAnchorHeightInPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
float p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.7, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (14);
a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(13.945, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(6.945, p, 0.001);
a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(0.054, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(14.0, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (12);
a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.372, p, 0.001);
}
/// <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));
}
}
static void Main(string[] args)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet s1=workbook.CreateSheet("Sheet1");
//set A2
s1.CreateRow(1).CreateCell(0).SetCellValue(-5);
//set B2
s1.GetRow(1).CreateCell(1).SetCellValue(1111);
//set C2
s1.GetRow(1).CreateCell(2).SetCellValue(7.623);
//set A3
s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);
//set A4=A2+A3
s1.CreateRow(3).CreateCell(0).CellFormula = "A2+A3";
//set D2=SUM(A2:C2);
s1.GetRow(1).CreateCell(3).CellFormula = "SUM(A2:C2)";
//set A5=cos(5)+sin(10)
s1.CreateRow(4).CreateCell(0).CellFormula="cos(5)+sin(10)";
//create another sheet
ISheet s2 = workbook.CreateSheet("Sheet2");
//set cross-sheet reference
s2.CreateRow(0).CreateCell(0).CellFormula = "Sheet1!A2+Sheet1!A3";
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(workbook);
var cell = e.Evaluate(cell);
WriteToFile();
}
public HSSFCreationHelper(HSSFWorkbook wb)
{
workbook = wb;
// Create the things we only ever need one of
dataFormat = new HSSFDataFormat(workbook.Workbook);
}
/// <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);
//写入到客户端
}
public void TestOpenWORKBOOK()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsA);
POIFSFileSystem fs = new POIFSFileSystem(is1);
// Ensure that we have a WORKBOOK entry
fs.Root.GetEntry("WORKBOOK");
// And a summary
fs.Root.GetEntry("\x0005SummaryInformation");
Assert.IsTrue(true);
// But not a Workbook one
try
{
fs.Root.GetEntry("Workbook");
Assert.Fail();
}
catch (FileNotFoundException)
{
}
// Try to Open the workbook
HSSFWorkbook wb = new HSSFWorkbook(fs);
}
/*
* 响应到客户端
*
* 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();
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheetCount"></param>
//protected override void FillData(Microsoft.Office.Interop.Excel.Workbook workBook, int sheetCount)
//{
// int rowCount = Dt.Rows.Count;
// int colCount = Dt.Columns.Count;
// for (int i = 1; i <= sheetCount; i++)
// {
// int startRow = (i - 1) * Rows;
// int endRow = i * Rows;
// if (i == sheetCount)
// {
// endRow = rowCount;
// }
// Worksheet sheet = (Worksheet)workBook.Worksheets.get_Item(i);
// sheet.Name = this.SheetPrefixName + "-" + i.ToString();
// for (int j = 0; j < endRow - startRow; j++)
// {
// for (int k = 0; k < colCount; k++)
// {
// if (k < 2)
// {
// sheet.Cells[Top + j, Left + k] = Dt.Rows[startRow + j][k].ToString();
// }
// else
// {
// sheet.Cells[Top + j, Left + k + 2] = Dt.Rows[startRow + j][k].ToString();
// }
// }
// }
// SetCellParameters(sheet);
// }
//}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheetCount"></param>
protected override void FillData(NPOI.HSSF.UserModel.HSSFWorkbook workBook, int sheetCount)
{
int rowCount = Dt.Rows.Count;
int colCount = Dt.Columns.Count;
for (int i = 0; i < sheetCount; i++)
{
int startRow = i * Rows;
int endRow = (i + 1) * Rows;
if ((i + 1) == sheetCount)
{
endRow = rowCount;
}
HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(i);
workBook.SetSheetName(i, this.SheetPrefixName + "-" + i.ToString());
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
if (k < 2)
{
sheet.GetRow(Top + j).GetCell(Left + k).SetCellValue(Dt.Rows[startRow + j][k].ToString());
}
else
{
sheet.GetRow(Top + j).GetCell(Left + k + 2).SetCellValue(Dt.Rows[startRow + j][k].ToString());
}
}
}
SetCellParameters(sheet);
}
}
//private static POILogger log = POILogFactory.GetLogger(typeof(HSSFSheet));
/// <summary>
/// Creates new HSSFSheet - called by HSSFWorkbook to create a _sheet from
/// scratch. You should not be calling this from application code (its protected anyhow).
/// </summary>
/// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
/// <see cref="NPOI.HSSF.UserModel.HSSFWorkbook.CreateSheet()"/>
public HSSFSheet(HSSFWorkbook workbook)
{
_sheet = InternalSheet.CreateSheet();
rows = new Dictionary<int, NPOI.SS.UserModel.IRow>();
this._workbook = workbook;
this.book = workbook.Workbook;
}
public NpoiLib(string FileTemplate)
{
using (FileStream file = new FileStream(FileTemplate, FileMode.Open, FileAccess.Read))
{
_workbook = new HSSFWorkbook(file);
}
}
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;
}
}
/// <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 TestEvaluateMissingArgs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("if(true,)");
fe.ClearAllCachedResultValues();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
}
// MissingArg -> BlankEval -> zero (as formula result)
Assert.AreEqual(0.0, cv.NumberValue, 0.0);
// MissingArg -> BlankEval -> empty string (in concatenation)
cell.CellFormula=("\"abc\"&if(true,)");
fe.ClearAllCachedResultValues();
Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
}
/// <summary>
/// Formats the COBie data into an Excel XLS file
/// </summary>
/// <param name="cobie"></param>
public void Serialise(COBieWorkbook workbook, ICOBieValidationTemplate ValidationTemplate = null)
{
if (workbook == null) { throw new ArgumentNullException("COBie", "COBieXLSSerialiser.Serialise does not accept null as the COBie data parameter."); }
if (!File.Exists(TemplateFileName))
throw new Exception("COBie creation error. Could not locate template file " + TemplateFileName);
// Load template file
FileStream excelFile = File.Open(TemplateFileName, FileMode.Open, FileAccess.Read);
XlsWorkbook = new HSSFWorkbook(excelFile, true);
CreateFormats();
foreach (var sheet in workbook)
{
WriteSheet(sheet);
}
UpdateInstructions();
ReportErrors(workbook, ValidationTemplate);
ReportRules();
using (FileStream exportFile = File.Open(FileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
{
XlsWorkbook.Write(exportFile);
}
}
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 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();
}
public void TestClean1()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
String[] asserts = {
"aniket\u0007\u0017\u0019", "aniket",
"\u0011aniket\u0007\u0017\u0010", "aniket",
"\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
"\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(\"" + asserts[i] + "\")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
asserts = new String[] {
"CHAR(7)&\"text\"&CHAR(7)", "text",
"CHAR(7)&\"text\"&CHAR(17)", "text",
"CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
"\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(" + asserts[i] + ")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
}
public ServiceLineExplorerExplorerExcelExporter(string serviceGroupName, MemoryStream output)
{
this.serviceGroupName = serviceGroupName;
this.output = output;
workbook = new HSSFWorkbook();
filterLists = new Dictionary<string, Dictionary<string, bool>>();
}
public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
{
IWorkbook result;
try
{
using (MemoryStream baos = new MemoryStream(8192))
{
wb.Write(baos);
using (Stream is1 = new MemoryStream(baos.ToArray()))
{
if (wb is HSSFWorkbook)
{
result = new HSSFWorkbook(is1);
}
else if (wb is XSSFWorkbook)
{
result = new XSSFWorkbook(is1);
}
else
{
throw new RuntimeException("Unexpected workbook type ("
+ wb.GetType().Name + ")");
}
}
}
}
catch (IOException e)
{
throw new RuntimeException(e);
}
return result;
}
public void Test27349()
{
// 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
HSSFWorkbook wb;
try
{
// original bug may have thrown exception here, or output warning to
// stderr
wb = new HSSFWorkbook(is1);
}
catch (IOException e)
{
throw new SystemException(e.Message);
}
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
// this defInitely would have failed due to 27349
Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
.CellFormula);
// We might as well Evaluate the formula
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Numeric, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}
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);
}
}
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 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 static ISheet GetExcelSheetForXLS(string file, string sheetName)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheet(sheetName);
return(sheet);
}
}
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));
}
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
}