NPO操作

/// <summary>
/// Excel取值
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static DataTable ImportExcelFile(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion

ISheet sheet = hssfworkbook.GetSheetAt(0);//读取第一个sheet页
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();

if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}

table.Rows.Add(dataRow);
}
return table;
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(NPOI.SS.UserModel.ICell cell)
{
if (cell == null)
return string.Empty;
if (cell.CellType == CellType.Numeric)
return cell.DateCellValue.ToString("yyyy/MM/dd");

switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}

/// <summary>
/// 操作World表格
/// </summary>
/// <param name="doc"></param>
/// <param name="m"></param>
public void SetTable(XWPFDocument doc,wordmodel m)
{
//取Excel 数据
string filepath = HttpContext.Server.MapPath("~/Word/excelnew.xls");
DataTable excleDb = ImportExcelFile(filepath);

//读取World表格
XWPFTable table = doc.Tables[0]; // doc.CreateTable(1, 4);//创建table
//table.RemoveRow(0);//去掉第一行空白的
table.SetColumnWidth(0, 6 * 256);//设置列的宽度
table.SetColumnWidth(1, 10 * 256);
table.SetColumnWidth(2, 6 * 256);
//table.SetColumnWidth(3, 10 * 256);
//table.SetColumnWidth(4, 6 * 256);
//table.SetColumnWidth(5, 10 * 256);

//插入World表格
for (int i = 0; i < excleDb.Rows.Count; i++)
{
DataRow row = excleDb.Rows[i];

CT_Row cr5 = new CT_Row();
XWPFTableRow tr5 = new XWPFTableRow(cr5, table);//创建行
tr5.GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)1000;
cr5.AddNewTrPr().AddNewTrHeight().val = (ulong)1000;
table.AddRow(tr5);//将行添加到table中
tr5.CreateCell().SetText(row[0].ToString());
tr5.CreateCell().SetText(row[1].ToString());
tr5.CreateCell().SetText(row[2].ToString());
//tr5.CreateCell().SetText("wwwww");
//tr5.CreateCell().SetText("");
//tr5.CreateCell().SetText(m.city);
tr5.GetCell(0).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
tr5.GetCell(1).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
tr5.GetCell(2).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
//tr5.GetCell(3).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
//tr5.GetCell(4).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
}
//写入World表格
doc.SetTable(0, table);
}
public ActionResult Index()
{

{
#region DataTable对象创建演示
DataTable table1 = new DataTable();
DataColumn c1 = new DataColumn("name", typeof(string));
DataColumn c2 = new DataColumn("id", typeof(int));
DataColumn c3 = new DataColumn("age", typeof(int));
table1.Columns.Add(c1);
table1.Columns.Add(c2);
table1.Columns.Add(c3);
DataRow row = table1.NewRow();
row[0] = "tom";
row["id"] = 1;
row[2] = 21;
table1.Rows.Add(row);
#endregion
}
return View();
}

/// <summary>
/// 上传文件
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
[HttpPost]
public ActionResult Index(wordmodel m)
{
UpFile();

#region 遍历Word文档中的信息
string filepath = HttpContext.Server.MapPath("~/Word/simpleTable.docx");
using (System.IO.FileStream stream = System.IO.File.OpenRead(filepath))
{
XWPFDocument doc = new XWPFDocument(stream);

//遍历段落
foreach (var para in doc.Paragraphs)
{
ReplaceKey(para, m);
} //遍历表格
var tables = doc.Tables;
foreach (var table in tables)
{
foreach (var row in table.Rows)
{
foreach (var cell in row.GetTableCells())
{
foreach (var para in cell.Paragraphs)
{
ReplaceKey(para, m);
}
}
}
}
SetTable(doc, m);
System.IO.FileStream out1 = new System.IO.FileStream(HttpContext.Server.MapPath("~/Word/simpleTable" + DateTime.Now.Ticks + ".docx"), System.IO.FileMode.Create);
doc.Write(out1);
out1.Close();

}

#endregion
return View();
}

/// <summary>
/// 上传文件
/// </summary>
private void UpFile()
{
string saveFilePath = HttpContext.Server.MapPath("~/Word/");
var file = Request.Files[0];
file.SaveAs(saveFilePath + "excelnew.xls");
}

/// <summary>
/// 找到匹配且替换
/// </summary>
/// <param name="para"></param>
/// <param name="model"></param>
private static void ReplaceKey(XWPFParagraph para, object model)
{
string text = para.ParagraphText;
var runs = para.Runs;
string styleid = para.Style;
for (int i = 0; i < runs.Count; i++)
{
var run = runs[i];
text = run.ToString();
Type t = model.GetType();
PropertyInfo[] pi = t.GetProperties();
foreach (PropertyInfo p in pi)
{
//$$与模板中$$对应，也可以改成其它符号，比如{$name},务必做到唯一
if (text.Contains("{$" + p.Name + "}"))
{
text = text.Replace("{$" + p.Name + "}", p.GetValue(model, null).ToString());
}
}
runs[i].SetText(text, 0);
}
}