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();
}
protected override HSSFWorkbook InternalGenerate()
{
var workbook = new HSSFWorkbook(new MemoryStream(Templates.ExceptionSchedule));
styles = new StandardCellStyles(workbook);
AddDataToReport(workbook.GetSheetAt(0), GetDefaultExceptionScheduleData());
AddDataToReport(workbook.GetSheetAt(1), GetServiceExceptionScheduleData(), false);
return workbook;
}
static void LoadMissionData(string path,MasterShop data)
{
using (FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
IWorkbook book = new HSSFWorkbook(stream);
ISheet sheet = book.GetSheetAt(0);
Debug.Log(sheet.SheetName);
IRow row0 = sheet.GetRow(0);
//一番最初のフィールドは見出しなので無視
for (int i = 1; i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
//エクセルデータを編集したらこことMasterCharacterに追加すれば更新できるよ
MasterShop.param p = new MasterShop.param();
p.id = (int)row.GetCell(0).NumericCellValue;
p.name = row.GetCell(1).StringCellValue;
p.subscripsion = row.GetCell(2).StringCellValue;
p.gold = (int)row.GetCell(3).NumericCellValue;
p.hot = (int)row.GetCell(4).NumericCellValue;
p.stress = (int)row.GetCell(5).NumericCellValue;
p.category = (int)row.GetCell(6).NumericCellValue;
p.res = row.GetCell(7).StringCellValue;
p.color = row.GetCell(8).StringCellValue;
p.gread = (int)row.GetCell(9).NumericCellValue;
data.list.Add(p);
}
}
}
public void TestSetGetCoordinates()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
Assert.AreEqual(group.X1, 0);
Assert.AreEqual(group.Y1, 0);
Assert.AreEqual(group.X2, 1023);
Assert.AreEqual(group.Y2, 255);
group.SetCoordinates(1, 2, 3, 4);
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
group = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
}
public void TestAddToExistingFile()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());
group1.SetCoordinates(1, 2, 3, 4);
group2.SetCoordinates(5, 6, 7, 8);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
group3.SetCoordinates(9, 10, 11, 12);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
}
/*
* 响应到客户端
*
* 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="excelFile">文件路径</param>
/// <param name="sheetIndex">0 start</param>
/// <param name="skipRow">0 start</param>
public void Read(Stream excelFile, int sheetIndex, int skipRow)
{
using (excelFile)
{
var workbook = new HSSFWorkbook(excelFile, false);
ISheet sheet = workbook.GetSheetAt(0);
int start = sheet.FirstRowNum > skipRow ? sheet.FirstRowNum : skipRow;
int end = sheet.LastRowNum;
if (start >= end)
{
return;
}
for (int i = start; i <= end; i++)
{
IRow row = sheet.GetRow(i);
var array = new List<ICell>();
int cellsNumber = row.Cells.Count;
for (short cellIndex = row.FirstCellNum; cellIndex <= cellsNumber; cellIndex++)
{
array.Add(row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK));
}
List<ValidationResult> result;
_process.Process(array.ToArray(), out result);
RowsResult.Add(result);
}
}
}
public void Transfer()
{
string oldExcelPath = string.Empty;
/*erp标准格式*/
string xslmodelPath = string.Empty;
FileStream fsModel = new FileStream(xslmodelPath, FileMode.Open);
HSSFWorkbook xslmodel = new HSSFWorkbook(fsModel);
var modelSheet = xslmodel.GetSheetAt(0);
/*现有的格式*/
using (FileStream fs = new FileStream(oldExcelPath, FileMode.Open))
{
HSSFWorkbook oldbook = new HSSFWorkbook(fs);
var firstSheet = oldbook.GetSheetAt(0);
System.Collections.IEnumerator rows = firstSheet.GetRowEnumerator();
//遍历每一行
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
//TODO::Create DataTable row
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
FillIntoModel((HSSFSheet)modelSheet, cell);
}
}
}
}
//读取xls文件
private ActionResult ReadExcel()
{
StringBuilder sbr = new StringBuilder();
FileStream fs = new FileStream(@"f:/test.xls",FileMode.Open);//打开myxls.xls文件
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
{
ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
if (row != null)
{
sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
{
ICell cell = row.GetCell(k); //当前表格
if (cell != null)
{
sbr.Append(cell.ToString()); //获取表格中的数据并转换为字符串类型
}
}
}
}
}
sbr.ToString();
StreamWriter wr = new StreamWriter(new FileStream(@"f:/myText.txt", FileMode.Append)); //把读取xls文件的数据写入myText.txt文件中
wr.Write(sbr.ToString());
wr.Flush();
return View();
}
Workbook CreateWorkbook(HSSFWorkbook workbook)
{
var worksheets = new List<Worksheet>();
for (int i = 0; i < workbook.NumberOfSheets; i++)
worksheets.Add(CreateWorksheet(workbook.GetSheetAt(i)));
return new Workbook(worksheets);
}
/// <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>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表索引</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex, int headerColIndex = 0)
{
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
DataTable table = new DataTable();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerColIndex; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + headerColIndex - 1;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = headerColIndex; j < cellCount; j++)
{
dataRow[j - headerColIndex] = row.GetCell(j) == null ? string.Empty : row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
public void TestGetPatriarch()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
Assert.IsNull(sh.DrawingPatriarch);
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
Assert.IsNotNull(patriarch);
patriarch.CreateSimpleShape(new HSSFClientAnchor());
patriarch.CreateSimpleShape(new HSSFClientAnchor());
Assert.AreSame(patriarch, sh.DrawingPatriarch);
EscherAggregate agg = patriarch.GetBoundAggregate();
EscherDgRecord dg = agg.GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
int lastId = dg.LastMSOSPID;
Assert.AreSame(patriarch, sh.CreateDrawingPatriarch());
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
dg = patriarch.GetBoundAggregate().GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
Assert.AreEqual(lastId, dg.LastMSOSPID);
}
/// <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);
}
}
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));
}
}
}
/// <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);
}
protected void GSD_btn_Click(object sender, EventArgs e)
{
try
{
this.workbook = new HSSFWorkbook(PYS_FileUpload.FileContent);
this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0); //取得第0個Sheet
//不同於Microsoft Object Model，NPOI都是從索引0開始算起
//從第一個Worksheet讀資料
SaveOrInsertSheet(this.u_sheet);
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true);
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true);
}
finally
{
//釋放 NPOI的資源
if (this.workbook != null) this.workbook = null;
if (this.u_sheet != null) this.u_sheet = null;
//是否刪除Server上的Excel檔(預設true)
/*bool isDeleteFileFromServer = false;
if (isDeleteFileFromServer)
{
System.IO.File.Delete(excel_filePath);
}*/
GC.Collect();
}
}
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 static void GetSheet(Stream stream)
{
IWorkbook workbook = new HSSFWorkbook(stream);//从流内容创建Workbook对象
ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作表
IRow row = sheet.GetRow(0);//获取工作表第一行
ICell cell = row.GetCell(0);//获取行的第一列
string value = cell.ToString();//获取列的值
}
/**
*
* Param templateFilePath 模版文件路径
*
*/
protected virtual void init(String templateFilePath)
{
FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);
this.workbook = new HSSFWorkbook(file);
this.sheet = workbook.GetSheetAt(0);
this.sheet.ForceFormulaRecalculation = true;
}
public static Sheet OpenWorkbook(string fileName)
{
var stream = new FileStream(fileName, FileMode.Open);
var workbook = new HSSFWorkbook(stream);
var sheet = workbook.GetSheetAt(0);
return sheet;
}
public WorksheetXls(HSSFWorkbook package, int sheet_number)
{
--sheet_number;
if (package.Workbook.NumSheets <= sheet_number && sheet_number >= 0)
{
throw new Exception("Sheet number is not valid");
}
workbook = package.GetSheetAt(sheet_number);
}
public void CargarArchivo()
{
System.Diagnostics.Debugger.Launch();
var flujo=File.OpenRead("C:\\output.xls");
HSSFWorkbook libro = new HSSFWorkbook(flujo, false);
var hoja = libro.GetSheetAt(0);
var fila = hoja.GetRow(1);
var celda=fila.GetCell(2);
}
/// <summary>
/// 将Excel文件中指定sheet索引的数据读出到DataTable中(xls)
/// </summary>
/// <param name="FileName">Excel文件名称</param>
/// <param name="SheetIndex">Sheet索引</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string FileName, int SheetIndex=0)
{
DataTable dt = new DataTable();
if (File.Exists(FileName))
{
try
{
using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(SheetIndex);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
sheet = null;
hssfworkbook = null;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
else dt = null;
return dt;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
HSSFWorkbook excel = new HSSFWorkbook(fileUpload.PostedFile.InputStream);
Sheet sheet = excel.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
ImportHelper.JumpRows(rows, 1);
IList<TransportationActBill> tactbillList = new List<TransportationActBill>();
IList<string> orderNos = new List<string>();
string supply = string.Empty;
while (rows.MoveNext())
{
Row curow = (HSSFRow)rows.Current;
string orderNo = curow.GetCell(0).StringCellValue;
// decimal cur = decimal.Parse(curow.GetCell(1).NumericCellValue.ToString());
if (string.IsNullOrEmpty(orderNo))
{
break;
}
else
{
if (orderNos.Contains(orderNo))
{
continue;//避免重复
}
else
{
orderNos.Add(orderNo);
}
}
}
string successMessage="计价成功的单号：";
string errorMessage="计价失败的单号：";
if (orderNos.Count > 0)
{
foreach (var orderNo in orderNos)
{
try
{
TheTransportationOrderMgr.ValuateTransportationOrder(orderNo, this.CurrentUser);
successMessage += orderNo + ",";
}
catch (Exception ex)
{
errorMessage += orderNo + ",";
}
}
ShowErrorMessage(successMessage+" </br> "+errorMessage);
}
else
{
ShowErrorMessage(" 导入的有效数据为0行！");
}
}
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);
}
}
public void TestBasicCopyTo()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
HSSFWorkbook bookC = new HSSFWorkbook();
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
sheetA.CreateRow(0).CreateCell(0).SetCellValue("Data in the first book");
HSSFSheet sheetB = bookB.CreateSheet("Sheet B") as HSSFSheet;
sheetB.CreateRow(0).CreateCell(0).SetCellValue("Data in the second book");
//Ensure that we can copy into a book that already has a sheet, as well as one that doesn't.
sheetA.CopyTo(bookB, "Copied Sheet A", false, false);
sheetA.CopyTo(bookC, "Copied Sheet A", false, false);
//Ensure the sheet was copied to the 2nd sheet of Book B, not the 1st sheet.
Assert.AreNotEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(1).GetRow(0).GetCell(0).StringCellValue);
//Ensure the sheet was copied to the 1st sheet in Book C
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookC.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
}
/**
*
* Param templateFileName 模版文件名
*
*/
protected virtual void init(String templateFileName)
{
this.reportTemplateFolder = this.reportTemplateFolder.Replace("\r\n\t\t", "");
string templateFile = reportTemplateFolder + templateFileName;
FileStream file = new FileStream(templateFile, FileMode.Open, FileAccess.Read);
this.workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
this.sheet = workbook.GetSheetAt(0);
this.sheet.ForceFormulaRecalculation = true;
}
public void ImportMilestones(string templateName, System.Web.HttpPostedFileBase[] xls)
{
if (xls.Length == 0)
{
_logger.WarnFormat("【ImportMilestones】没有上传任何里程碑模板文件！");
return;
}
if (_context.MilestoneTemplates.Query.Any(m => m.Name == templateName))
{
var errorMessage = string.Format("系统中已经有名称为【{0}】的里程碑模板了！", templateName);
_logger.Error(errorMessage);
throw new InvalidOperationException(errorMessage);
}
var template = new MilestoneTemplate() { Name = templateName };
var workbook = new HSSFWorkbook(xls[0].InputStream);
var worksheet = workbook.GetSheetAt(0);
var rowIdx = 0;
var lastRowIdx = worksheet.LastRowNum;
while (rowIdx <= lastRowIdx)
{
rowIdx++;
var row = worksheet.GetRow(rowIdx);
string name = null;
double duration = 0.0d;
try
{
name = row.GetCell(0).StringCellValue;
}
catch (Exception e)
{
_logger.WarnFormat("【ImportMilestones】导入里程碑模板\"{0}\"的第{1}行出错，其没有里程碑工期的数据，错误原因：{2}\n堆栈：{3}",
xls[0].FileName, rowIdx + 1, e.Message, e.StackTrace);
continue;
}
try
{
duration = row.GetCell(1).NumericCellValue;
}
catch (Exception e)
{
_logger.WarnFormat("【ImportMilestones】导入里程碑模板\"{0}\"的第{1}行出错，其没有里程碑工期的数据，错误原因：{2}\n堆栈：{3}",
xls[0].FileName, rowIdx + 1, e.Message, e.StackTrace);
continue;
}
var item = new MilestoneTemplateItem() { Name = name, Distance = (int)duration, DistanceUnit = (int)DistanceUnit.ByWeeks };
template.Milestones.Add(item);
}
_context.MilestoneTemplates.Add(template);
_context.SaveChanges();
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets) {
if (!filePath.Equals (asset))
continue;
PlayerLevelData data = (PlayerLevelData)AssetDatabase.LoadAssetAtPath (exportPath, typeof(PlayerLevelData));
if (data == null) {
data = ScriptableObject.CreateInstance<PlayerLevelData> ();
AssetDatabase.CreateAsset ((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.list.Clear ();
using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read)) {
IWorkbook book = new HSSFWorkbook (stream);
ISheet sheet = book.GetSheetAt (0);
for (int i=1; i< sheet.LastRowNum; i++) {
IRow row = sheet.GetRow (i);
ICell cell = null;
PlayerLevelData.Param p = new PlayerLevelData.Param ();
cell = row.GetCell(0); p.level = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(1); p.hpMax = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(2); p.hungerMax = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(3); p.agility = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.exp = (int)(cell == null ? 0 : cell.NumericCellValue);
p.ApMax = new int[8];
cell = row.GetCell(5); p.ApMax[0] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(6); p.ApMax[1] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(7); p.ApMax[2] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(8); p.ApMax[3] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(9); p.ApMax[4] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(10); p.ApMax[5] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(11); p.ApMax[6] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(12); p.ApMax[7] = (int)(cell == null ? 0 : cell.NumericCellValue);
p.DpMax = new int[8];
cell = row.GetCell(13); p.DpMax[0] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(14); p.DpMax[1] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(15); p.DpMax[2] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(16); p.DpMax[3] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(17); p.DpMax[4] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(18); p.DpMax[5] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(19); p.DpMax[6] = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(20); p.DpMax[7] = (int)(cell == null ? 0 : cell.NumericCellValue);
data.list.Add (p);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath (exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty (obj);
}
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static string GetRowCellData(string file, int rowIndex, int cellIndex)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
var cell = sheet.GetRow(rowIndex).GetCell(cellIndex);
return(cell.StringCellValue);
}
}
public static Row GetRowData(ExcelExport sut, int rowIndex)
{
using (MemoryStream output = new MemoryStream())
{
sut.ExportToStream(output);
HSSFWorkbook workbook = new HSSFWorkbook(output);
return workbook.GetSheetAt(0).GetRow(rowIndex);
}
}
protected void Btn_AddIn_Click(object sender, EventArgs e)
{
try
{
string strFileName = this.File_Upload.FileName;
//验证是否选择了文件
if ("" == strFileName.Trim())
{
throw new Exception("没有选择文件");
}
//验证文件类型是不是Excel
//if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xlsx" && strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
//{
// throw new Exception("文件类型错误");
//}
if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
{
throw new Exception("文件类型错误");
}
//获取上载文件内容
var fileStream = File_Upload.PostedFile.InputStream;
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
if (wb == null)
{
throw new Exception("Excel表格数据为空");
}
HSSFSheet sheet = wb.GetSheetAt(0) as HSSFSheet;
var re0 = TH_HAZALOCA.BuildListByNPOISheet(sheet);
if (!re0.Flag)
{
throw new Exception(re0.Msg);
}
if (re0.Flag)
{
Notify.ShowMessage("成功添加条数:" + re0.Msg);
}
else
{
throw new Exception(re0.Msg);
}
}
catch (Exception ex)
{
Notify.ShowMessage(ex.Message);
return;
}
}
/// <summary>
/// Opens the specified file.
/// </summary>
/// <param name="fileName">Name of the file.</param>
public void OpenFile(string fileName)
{
IsFileValid();
//Openning the file
_openedFile = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//Load the template into a NPOI workbook
_workbook = new HSSFWorkbook(_openedFile, true);
//Getting the sheet
_sheet = _workbook.GetSheetAt(0);
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List <int> columns = new List <int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as XLS.HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
try
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as XLS.HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
catch { }
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return(dt);
}
private HSSFWorkbook CreateEmptyWorkbook(string subject, string sheetName)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Adam Nowak";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
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(sheetName);
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
return hssfworkbook;
}
static void Main(string[] args)
{
var path = @"d.xls";
using (var fs = File.OpenRead(path))
{
//FunctionEval.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу
//WorkbookEvaluator.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу
var wb = new HSSFWorkbook(fs);
#region 1 way
//new CustomFunctionEval();
#endregion
#region 2 way
/* можно добавлять только новые функции. round подменить не выйдет
String[] functionNames = { "ROUND2" };
FreeRefFunction[] functionImpls = { new ICDBUDFRound() };
UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);
wb.AddToolPack(udfToolpack);
*/
#endregion
#region 3 way
//FunctionEvlEx.RegisterFunction("round", new ICDBRound());
#endregion
var eval = wb.GetCreationHelper().CreateFormulaEvaluator();
var sheet = wb.GetSheetAt(0);
var en = sheet.GetRowEnumerator();
while (en.MoveNext())
{
var row = (IRow)en.Current;
var orig = row.GetCell(0);
var charp = Math.Round(decimal.Parse(orig.ToString()), 2, MidpointRounding.AwayFromZero);
var formula = row.GetCell(2);
var npoiRound = eval.Evaluate(formula).NumberValue;
var excel = row.GetCell(1);
formula.CellFormula = formula.CellFormula.Replace("ROUND(", "ROUNDUP(");
eval.ClearAllCachedResultValues();
var npoiRoundUp = eval.Evaluate(formula).NumberValue;
Console.WriteLine("orig = {0}; excel = {1}; charp (MidpointRounding.AwayFromZero) = {2}; npoi(ROUND) = {3}; npoi(ROUNDUP) = {4}", orig, excel, charp, npoiRound, npoiRoundUp);
}
}
}
/// <summary>
/// 读取明源的报表
/// </summary>
/// <returns></returns>
private static List <Model> ReadRep(string change_File)
{
Model model;
int rowNum;
List <Model> lst = new List <Model>();
//从明源获取实收文件
// string file_old = @"c:\Users\1\Downloads\实收款项明细表.xls";
change_File = change_File.Split('.')[0] + ".xls";
using (FileStream fs_old = new FileStream(change_File, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook_old = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_old);
ISheet sheet_old = workbook_old.GetSheetAt(0);
rowNum = sheet_old.LastRowNum;
for (int i = 8; i < rowNum; i++)
{
model = new Model();
model.序号 = sheet_old.GetRow(i).GetCell(1).NumericCellValue.ToString();
model.项目名称 = sheet_old.GetRow(i).GetCell(2).StringCellValue;
model.楼栋名称 = sheet_old.GetRow(i).GetCell(5).StringCellValue;
// CellType ct = sheet_old.GetRow(i).GetCell(14).CellType;
model.房号 = sheet_old.GetRow(i).GetCell(9).StringCellValue;
model.客户名称 = sheet_old.GetRow(i).GetCell(11).StringCellValue;
model.收款日期 = sheet_old.GetRow(i).GetCell(14).DateCellValue;
model.票据类型 = sheet_old.GetRow(i).GetCell(17).StringCellValue;
model.票据编号 = sheet_old.GetRow(i).GetCell(18).StringCellValue;
model.款项类型 = sheet_old.GetRow(i).GetCell(19).StringCellValue;
model.款项名称 = sheet_old.GetRow(i).GetCell(20).StringCellValue;
model.金额 = sheet_old.GetRow(i).GetCell(22).NumericCellValue;
model.支付方式 = sheet_old.GetRow(i).GetCell(25).StringCellValue;
model.银付方式 = sheet_old.GetRow(i).GetCell(27).StringCellValue;
model.摘要 = sheet_old.GetRow(i).GetCell(26).StringCellValue;
lst.Add(model);
}
workbook_old.Close();
}
return(lst);
}
/// <summary>
/// 加载Excel模板样式(xls)
/// </summary>
/// <param name="file"></param>
private static void LoadExcelStyleForXLS(string file)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet s = hssfworkbook.GetSheetAt(0);
for (int i = s.FirstRowNum; i <= s.LastRowNum; i++)
{
ICell c0 = s.GetRow(i).GetCell(0);
ICell c1 = s.GetRow(i).GetCell(1);
CellStyles.Add(c0.StringCellValue, c1.CellStyle);
if (c0.StringCellValue == "DateTime")
{
IDataFormat format = hssfworkbook.CreateDataFormat();
CellStyles["DateTime"].DataFormat = format.GetFormat(c1.StringCellValue);
}
}
}
}
public override void DoLoginedHandlerWork(HttpContext context)
{
Message jsonMessage;
jsonMessage = new Message()
{
Result = false,
TxtMessage = "权限验证失败，可能原因:\n1、数据中心通讯失败。\n2、系统管理员未与您分配对应操作权限。"
};
//获取操作类型AType:ADD,EDIT,DELETE
string AjaxType = context.Request.QueryString["AType"] == null ? string.Empty : context.Request.QueryString["AType"].ToString().ToUpper();
WCFServiceProxy <IUserManage> proxy = null;
try
{
proxy = new WCFServiceProxy <IUserManage>();
if (AjaxType == "UPLOAD")
{
if (context.Request.Files != null && context.Request.Files.Count == 1)
{
HttpPostedFile postedFile = context.Request.Files[0];
DataTable dt = null;
Message m;
List <IoT_UserTemp> list = new List <IoT_UserTemp>();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(postedFile.InputStream);
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.NewRow();
for (int j = firstCellNum; j < lastCellNum; j++)
{
NPOI.SS.UserModel.ICell cell = sheet.GetRow(i).GetCell(j);
if (cell == null)
{
newRow[j] = "";
}
else
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
case NPOI.SS.UserModel.CellType.Unknown:
newRow[j] = "";
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
newRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
newRow[j] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
newRow[j] = cell.StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
newRow[j] = cell.CellFormula;
break;
case NPOI.SS.UserModel.CellType.Boolean:
newRow[j] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
newRow[j] = "";
break;
default:
newRow[j] = "";
break;
}
}
}
if (lastCellNum < 8)
{
continue;
}
decimal meterNum = decimal.TryParse(newRow[3].ToString(), out meterNum) ? meterNum : 0;
IoT_UserTemp gas = new IoT_UserTemp()
{
UserName = newRow[0].ToString(),
UserID = newRow[1].ToString(),
MeterNo = newRow[2].ToString(),
MeterNum = meterNum,
Street = newRow[4].ToString(),
Community = newRow[5].ToString(),
Door = newRow[6].ToString(),
Address = newRow[7].ToString(),
CompanyID = loginOperator.CompanyID
};
/*
* Direction = newRow[8].ToString(),
* InstallType = newRow[9].ToString(),
* Phone = newRow[10].ToString(),
* UserType = newRow[11].ToString() =="0"?"0":"1",
* InstallDate = newRow[12].ToString(), */
if (lastCellNum >= 9)
{
gas.Direction = newRow[8].ToString();
}
if (lastCellNum >= 10)
{
gas.InstallType = newRow[9].ToString();
}
if (lastCellNum >= 11)
{
gas.Phone = newRow[10].ToString();
}
if (lastCellNum >= 12)
{
gas.UserType = newRow[11].ToString();
}
if (lastCellNum >= 13)
{
try
{
gas.InstallDate = Convert.ToDateTime(newRow[12].ToString()).ToString("yyyy-MM-dd");
}
catch { }
}
m = proxy.getChannel.AddTemp(gas);
if (!m.Result)
{
list.Add(gas);
}
}
}
jsonMessage = new Message()
{
Result = true,
TxtMessage = Newtonsoft.Json.JsonConvert.SerializeObject(list)
};
}
}
}
catch (Exception ex) {
jsonMessage = new Message()
{
Result = false,
TxtMessage = ex.Message
};
}
finally
{
proxy.CloseChannel();
}
context.Response.Write(JSon.TToJson <Message>(jsonMessage));
}
/// <summary>
/// 文件导入函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Btn_AddInRISK_Click(object sender, System.EventArgs e)
{
try
{
string strFileName = this.File_Upload.FileName;
//验证是否选择了文件
if ("" == strFileName.Trim())
{
throw new Exception("没有选择文件");
}
//验证文件类型是不是Excel
//if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xlsx" && strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
//{
// throw new Exception("文件类型错误");
//}
if (strFileName.Substring(strFileName.LastIndexOf('.')) != ".xls")
{
throw new Exception("文件类型错误");
}
//获取上载文件内容
var fileStream = File_Upload.PostedFile.InputStream;
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
if (wb == null)
{
throw new Exception("Excel表格数据为空");
}
HSSFSheet sheet = wb.GetSheetAt(0) as HSSFSheet;
var userid = base.GetIdentityName();
var re0 = TH_THAZA01.BuildListByNPOISheet(sheet, userid);
if (!re0.Flag)
{
throw new Exception(re0.Msg);
}
foreach (var in_hazard in re0.Entity)
{
foreach (var in_risk in in_hazard.RISKLIST)
{
var re = in_risk.CheckAndSaveRiskinDB();
if (!re.Flag)
{
throw new Exception(re.Msg);
}
}
var re1 = in_hazard.AddSaveEntitytoDB();
if (!re1.Flag)
{
throw new Exception(re1.Msg);
}
}
Notify.ShowMessage("成功添加所有条目");
return;
}
catch (Exception ex)
{
Notify.ShowMessage(ex.Message);
return;
}
}
/// <summary>
/// 根据模板创建新的文件
/// </summary>
/// <param name="lst"></param>
private static void CreatReport(List <Model> lst)
{
#region 加载xls文件
//模板文件路径
string path = @"C:\Users\1\Desktop\日报\实收款项明细表.xls";
FileStream fs_modle;
using (fs_modle = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook_model = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_modle);
// fs_modle.Close();
ISheet sheet_model = workbook_model.GetSheetAt(0);
IRow row_hj = sheet_model.GetRow(7);//合计行
//设置单元格时
ICellStyle style = workbook_model.CreateCellStyle();
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.WrapText = true;
style.DataFormat = 4;
style.VerticalAlignment = VerticalAlignment.Center;
IRow r;
for (int i = 6; i < lst.Count + 6; i++)
{
r = sheet_model.CreateRow(i); //创建一新行
r.HeightInPoints = 25; //设置行高
for (int J = 0; J < 14; J++)
{
switch (J)
{
case 0:
r.CreateCell(J).SetCellValue(lst[i - 6].序号);
break;
case 1:
r.CreateCell(J).SetCellValue(lst[i - 6].项目名称);
break;
case 2:
r.CreateCell(J).SetCellValue(lst[i - 6].楼栋名称);
break;
case 3:
r.CreateCell(J).SetCellValue(lst[i - 6].房号);
break;
case 4:
r.CreateCell(J).SetCellValue(lst[i - 6].客户名称);
break;
case 5:
r.CreateCell(J).SetCellValue(lst[i - 6].收款日期.ToString());
break;
case 6:
r.CreateCell(J).SetCellValue(lst[i - 6].票据类型);
break;
case 7:
r.CreateCell(J).SetCellValue(lst[i - 6].票据编号);
break;
case 8:
r.CreateCell(J).SetCellValue(lst[i - 6].款项类型);
break;
case 9:
r.CreateCell(J).SetCellValue(lst[i - 6].款项名称);
break;
case 10:
r.CreateCell(J).SetCellValue(lst[i - 6].金额);
break;
case 11:
r.CreateCell(J).SetCellValue(lst[i - 6].支付方式);
break;
case 12:
r.CreateCell(J).SetCellValue(lst[i - 6].银付方式);
break;
case 13:
r.CreateCell(J).SetCellValue(lst[i - 6].摘要);
break;
default:
break;
}
}
var enu = r.GetEnumerator();
while (enu.MoveNext())
{
enu.Current.CellStyle = style;//设置风格
}
}
//创建合计行
IRow r1 = sheet_model.CreateRow(lst.Count + 6);
r1.HeightInPoints = 25;//设置行高
ICell cell;
IFont font = workbook_model.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
for (int i = 0; i < 14; i++)
{
cell = r1.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue("--");
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.SetFont(font);
}
double JE = lst.Sum(t => t.金额);
r1.GetCell(0).SetCellValue(" ");
r1.GetCell(1).SetCellValue("合计");
r1.GetCell(10).SetCellValue(JE);
//统计周期和统计时间
string ZQ = $"统计周期：{DateTime.Now.ToShortDateString()} 至 {DateTime.Now.ToShortDateString()}";
string DT = $"制表日期：{DateTime.Now.ToShortDateString()}";
sheet_model.GetRow(3).GetCell(0).SetCellValue(ZQ);
sheet_model.GetRow(4).GetCell(0).SetCellValue(DT);
string newFileFullPath = $@"{ Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)}";
string filePath = $"{newFileFullPath}\\日报\\日报表{DateTime.Now.ToShortDateString()}.xls";
try
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook_model.Write(fs);
}
// fs.Dispose();
Console.WriteLine("文件创建成功!");
// workbook_model.Close();
lst.Clear();
}
catch (Exception)
{
throw;
}
finally
{
Console.WriteLine(File.Exists(filePath));
Process.Start(@"C:\Program Files (x86)\Kingsoft\WPS Office\11.1.0.9912\office6\wps.exe", filePath);
}
}
#endregion
}
