/// <summary>
/// 将DATATABLE中的数据写入到xls中
/// </summary>
/// <param name="dta">表类型 弱类型</param>
/// <returns></returns>
///
public string Creating_Excel_Information(DataTable dta)
{
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿
workbook2003.CreateSheet("Sheet1"); //新建1个Sheet工作表
HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
//对工作表先添加行，下标从0开始
//MessageBox.Show(dta.Rows.Count.ToString());
//MessageBox.Show(dta.Rows[0][0].ToString());
for (int i = 0; i < dta.Rows.Count; i++)
{
IRow row = SheetOne.CreateRow(i);
//HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(i);
//HSSFCell[] SheetCell = new HSSFCell[3];
//SheetCell[0] = (HSSFCell)SheetRow.CreateCell(0);
//SheetCell[1] = (HSSFCell)SheetRow.CreateCell(1);
//SheetCell[2] = (HSSFCell)SheetRow.CreateCell(2);
//SheetCell[0].SetCellValue(dta.Rows[i][0].ToString());
//SheetCell[1].SetCellValue(dta.Rows[i][1].ToString());
//SheetCell[2].SetCellValue(dta.Rows[i][2].ToString());
//MessageBox.Show(dta.Rows[i][2].ToString());
row.CreateCell(0).SetCellValue(dta.Rows[i][0].ToString());
row.CreateCell(1).SetCellValue(dta.Rows[i][1].ToString());
row.CreateCell(2).SetCellValue(dta.Rows[i][2].ToString());
row.CreateCell(3).SetCellValue(dta.Rows[i][3].ToString());
row.CreateCell(4).SetCellValue(dta.Rows[i][4].ToString());
}
string saveAsPath = xlsSavePath + dta.Rows[0][1].ToString() + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + "_new.xls";
//string saveAsPath = xlsSavePath + dta.Rows[0][1].ToString() + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + "_new.xls";
FileStream file2003 = new FileStream(saveAsPath, FileMode.Create);
workbook2003.Write(file2003);
file2003.Close();
workbook2003.Close();
return(saveAsPath);
}
/// <summary>
/// Excel添加一行
/// </summary>
private void addExcel()
{
FileStream fs = new FileStream(savePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
POIFSFileSystem ps = new POIFSFileSystem(fs);
HSSFWorkbook wk = new HSSFWorkbook(ps);
ISheet tb = wk.GetSheet("Sheet01");
int number = tb.LastRowNum;
FileStream fs1 = new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
IRow row1 = tb.CreateRow(number + 1);
row1.CreateCell(0).SetCellValue(BingRen.StepSpeed);
row1.CreateCell(1).SetCellValue(BingRen.RightSpeedM);
row1.CreateCell(2).SetCellValue(BingRen.RightSpeedD);
row1.CreateCell(3).SetCellValue(BingRen.LeftSpeedM);
row1.CreateCell(4).SetCellValue(BingRen.LeftSpeedD);
row1.CreateCell(5).SetCellValue(BingRen.CyleSpeedM);
row1.CreateCell(6).SetCellValue(BingRen.CyleSpeedD);
row1.CreateCell(7).SetCellValue(BingRen.RightStepLengthM);
row1.CreateCell(8).SetCellValue(BingRen.RightStepLengthD);
row1.CreateCell(9).SetCellValue(BingRen.LeftStepLengthM);
row1.CreateCell(10).SetCellValue(BingRen.LeftStepLengthD);
row1.CreateCell(11).SetCellValue(BingRen.StepLengthBalance);
row1.CreateCell(12).SetCellValue(BingRen.RightStepHeightM);
row1.CreateCell(13).SetCellValue(BingRen.RightStepHeightD);
row1.CreateCell(14).SetCellValue(BingRen.LeftStepHeightM);
row1.CreateCell(15).SetCellValue(BingRen.LeftStepHeightD);
row1.CreateCell(16).SetCellValue(BingRen.StepHeightBalance);
row1.CreateCell(17).SetCellValue(BingRen.StepWidthM);
row1.CreateCell(18).SetCellValue(BingRen.StepWidthD);
row1.CreateCell(19).SetCellValue(BingRen.StepDistanceM);
row1.CreateCell(20).SetCellValue(BingRen.StepDistanceD);
row1.CreateCell(21).SetCellValue(BingRen.ZM);
row1.CreateCell(22).SetCellValue(BingRen.ZD);
row1.CreateCell(23).SetCellValue(BingRen.LeftFootAberrance);
row1.CreateCell(24).SetCellValue(BingRen.RightFootAberrance);
fs1.Flush();
wk.Write(fs1);
wk.Close();
fs1.Close();
}
public void TestNamedCell_1()
{
// setup for this Testcase
String sheetName = "Test Named Cell";
String cellName = "A name for a named cell";
String cellValue = "TEST Value";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue));
// Create named range for a single cell using areareference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cellName);
String reference = "'" + sheetName + "'" + "!A1:A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cellName);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
AreaReference aref = new AreaReference(aNamedCell.RefersToFormula);
Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'");
CellReference cref = aref.FirstCell;
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Assert.IsNotNull(s);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference");
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路径</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的数据</param>
/// <param name="coluids">需更新的列号</param>
/// <param name="rowid">需更新的开始行号</param>
public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
{
FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
readfile.Close();
ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
for (int j = 0; j < coluids.Length; j++)
{
for (int i = 0; i < updateData[j].Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
}
sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
}
catch (Exception ex)
{
}
}
}
try
{
FileStream writefile = new FileStream(outputFile, FileMode.Create);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
}
}
private async void btnLoad_Click(object sender, EventArgs e)
{
this.btnLoad.Enabled = false;
Stream fs = new FileStream("G:\\JIRA.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("All Case");
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; // 一行最后一个cell的编号 即总的列数
int rowCount = sheet.LastRowNum; // 最后一列的标号
int startRow = 1;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
string casenum = row.GetCell(1).ToString();
List <string> cases = new List <string>();
cases.Add(casenum);
var getCaselist = SalesforceProxy.GetCaseList(cases);
var caseList = await getCaselist;
foreach (var caseInfo in caseList)
{
if (caseInfo != null)
{
System.Console.WriteLine(casenum + "," + caseInfo.Origin);
}
}
}
}
this.btnLoad.Enabled = true;
}
/// <summary>
/// 更新Excel表格
/// </summary>
/// <param name="outputFile">需更新的excel表格路径</param>
/// <param name="sheetname">sheet名</param>
/// <param name="updateData">需更新的数据</param>
/// <param name="coluid">需更新的列号</param>
/// <param name="rowid">需更新的开始行号</param>
public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
{
FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
for (int i = 0; i < updateData.Length; i++)
{
try
{
if (sheet1.GetRow(i + rowid) == null)
{
sheet1.CreateRow(i + rowid);
}
if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
{
sheet1.GetRow(i + rowid).CreateCell(coluid);
}
sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());
throw;
}
}
try
{
readfile.Close();
FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());
}
}
public MemoryStream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
const int maxSheetCount = 65536;
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
int Count = sourceTable.Rows.Count / maxSheetCount;
for (int i = 0; i <= Count; i++)
{
ISheet sheet = workbook.CreateSheet(sheetName + i.ToString());
}
for (int i = 0; i <= Count; i++)
{
ISheet sheet = workbook.GetSheet(sheetName + i.ToString());
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
for (int j = maxSheetCount * i; j < maxSheetCount * (i + 1); j++)
{
if (j >= sourceTable.Rows.Count || rowIndex >= maxSheetCount)
{
break;
}
DataRow row = sourceTable.Rows[j];
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
return(ms);
}
private void Form2_Load(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = documentPath;
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
excelPath = openFileDialog1.FileName;
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet1 = workbook.GetSheet("КТ");
ttt = new Dictionary <int, string>();
int i = 9;
while (1 == 1)
{
if (sheet1.GetRow(i) == null)
{
i++;
}
if (string.IsNullOrEmpty(sheet1.GetRow(i).GetCell(0).StringCellValue) && (string.IsNullOrEmpty(sheet1.GetRow(i + 1).GetCell(0).StringCellValue)))
{
break;
}
ttt.Add(i, (sheet1.GetRow(i).GetCell(0).StringCellValue) + " " + (sheet1.GetRow(i).GetCell(12).NumericCellValue) + " семестр");
i++;
}
listBox1.DataSource = ttt.Values.ToList <string>();
listBox1.Refresh();
}
catch (Exception)
{
MessageBox.Show("Выберите соответствующий тип файла!");
this.Close();
}
}
}
public static async Task ParseXLS(string workBookPath)
{
try
{
ISheet sheet;
var fileExt = Path.GetExtension(workBookPath);
var fileFullPath = Path.GetFullPath(workBookPath);
if (fileExt != null && fileExt.ToLower() == ".xls")
{
HSSFWorkbook hssfwb;
using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read))
hssfwb = new HSSFWorkbook(file);
sheet = hssfwb.GetSheet(hssfwb.GetSheetName(0));
await MainCyrcle(sheet);
using (var file = new FileStream(fileFullPath, FileMode.Create))
hssfwb.Write(file);
}
else if (fileExt != null && fileExt.ToLower() == ".xlsx")
{
XSSFWorkbook xssfwb;
using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read))
xssfwb = new XSSFWorkbook(file);
sheet = xssfwb.GetSheet(xssfwb.GetSheetName(0));
await MainCyrcle(sheet);
using (var file = new FileStream(fileFullPath, FileMode.Create))
xssfwb.Write(file);
}
Informer.RaiseOnResultReceived($"{workBookPath} successfully saved");
}
catch (Exception ex)
{
Informer.RaiseOnResultReceived(ex);
}
}
public int DataTableToExcel(DataTable dt, string sheetName, bool isColumnWritten, bool Append = true)
{
try
{
HSSFWorkbook Workbook = NPOIOpenExcel(fileName);//打开工作薄
ISheet sheet = null;
if (sheetName != null)
{
sheet = Workbook.GetSheet(sheetName);
}
if (sheet == null)
{
sheet = Workbook.GetSheetAt(0);
}
if (sheet == null)
{
return(0);
}
int nStartRow = sheet.LastRowNum + 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
IRow row = sheet.CreateRow(nStartRow);
for (int j = 0; j < dt.Columns.Count; j++) //写一行的数据
{
row.CreateCell(j).SetCellValue(dr[j].ToString());
}
}
WriteToFile(Workbook, fileName);
}
catch (Exception e)
{
Messenger.Default.Send <string>(e.Message, "ShowError");
return(0);
}
return(1);
}
protected void btnImport_Click(object sender, EventArgs e)
{
bool isSuccess = false;
if (this.importFiles.PostedFile.ContentLength <= 0)
{
return;
}
Stream stream = this.importFiles.FileContent;
HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
string strSql = "";
ArrayList list = new ArrayList();
DataTable dt = RenderFromExcel(sheet, 0);
TBaseApparatusInfoVo tav = new TBaseApparatusInfoVo();
//string[] strArr;
for (int i = 0; i < dt.Rows.Count; i++)
{
tav.ID = GetSerialNumber("Apparatus_Id"); //ID
tav.IS_DEL = "0"; //是否删除 0=否，1=是
tav.APPARATUS_CODE = dt.Rows[i + 2][0].ToString().Trim(); //仪器编号
tav.NAME = dt.Rows[i + 2][1].ToString().Trim(); //仪器名称
tav.MODEL = dt.Rows[i + 2][3].ToString().Trim(); //型号规格
tav.MEASURING_RANGE = dt.Rows[i + 2][4].ToString().Trim(); //测量范围
tav.EXPANDED_UNCETAINTY = dt.Rows[i + 2][5].ToString().Trim(); //不确定度
tav.FITTINGS_PROVIDER = dt.Rows[i + 2][6].ToString().Trim(); //供应商
tav.SERIAL_NO = dt.Rows[i + 2][7].ToString().Trim(); //出厂编号
tav.BUY_TIME = dt.Rows[i + 2][8].ToString().Trim(); //购买时间
tav.REMARK2 = dt.Rows[i + 2][9].ToString().Trim(); //金额
tav.POSITION = dt.Rows[i + 2][10].ToString().Trim(); //放置地点（新地点）
tav.REMARK3 = dt.Rows[i + 2][11].ToString().Trim(); //放置地点（旧地点）
new TBaseApparatusInfoLogic().Create(tav);
}
labMsg.Text = "导入成功!!!!!!!!!!!!!!!!!!";
}
public static List <Data> GetData()
{
var answer = new List <Data>();
HSSFWorkbook book;
using (FileStream file = new FileStream(@"DataFile.xls", FileMode.Open, FileAccess.Read))
{
book = new HSSFWorkbook(file);
}
ISheet page = book.GetSheet("Page1");
for (int row = 1; row <= page.LastRowNum; row++)
{
var item = page.GetRow(row);
if (item != null) //null is when the row only contains empty cells
{
try
{
String Name = item.Cells[0].ToString();
String Url = item.Cells[3].ToString();
DateTime StartData = StringToDate(item.Cells[1].ToString());
DateTime EndData = StringToDate(item.Cells[2].ToString());
answer.Add(new Data(Name, StartData, EndData, Url));
}
catch (Exception e)
{
// im lazy to parse if some data is not correct or dont exist
// first of all, i dont know what i shall do
// put a message on it place like "Ещё не выбрано" or it ""?
// mush easer to skip this line
Console.Error.WriteLine("Cant parse xls row to Data");
Console.Error.WriteLine(e.Message);
}
}
}
return(answer);
}
/// <summary>
/// 从属性表生成excel文件
/// </summary>
/// <param name="dta"></param>
/// <param name="FilePath"></param>
/// <returns></returns>
public string Creating_Excel_From_DataTable(DataTable dta, string FilePath)
{
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿
workbook2003.CreateSheet("Sheet1"); //新建1个Sheet工作表
HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
for (int i = 0; i < dta.Rows.Count; i++)
{
IRow row = SheetOne.CreateRow(i);
row.CreateCell(0).SetCellValue(dta.Rows[i][0].ToString());
row.CreateCell(1).SetCellValue(dta.Rows[i][1].ToString());
row.CreateCell(2).SetCellValue(dta.Rows[i][2].ToString());
row.CreateCell(3).SetCellValue(dta.Rows[i][3].ToString());
row.CreateCell(4).SetCellValue(dta.Rows[i][4].ToString());
}
string saveAsPath = FilePath.Replace(".rfa", "") + ".xls";
FileStream file2003 = new FileStream(saveAsPath, FileMode.Create);
workbook2003.Write(file2003);
file2003.Close();
workbook2003.Close();
//eeeeee = saveAsPath;
//string xlsFilePath = @"C:\ProgramData\Autodesk\Revit\Addins\2016\xlsFilePath.txt";
FileStream fs = new FileStream(xlsFilePathLog, FileMode.Append, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
if (saveAsPath != null)
{
sw.Write(saveAsPath + "\n");
}
sw.Close();
fs.Close();
return(saveAsPath);
}
public ResultadoOperacion CleanHyperLinks(int col)
{
var ro = GetExcel();
if (!ro.Ok)
{
return(ro);
}
using (var fs = File.OpenRead(Excel))
{
hssfWorkbook = new HSSFWorkbook(fs);
}
var sheet = hssfWorkbook.GetSheet(Hoja);
var row = 1;
while (true)
{
var xlRow = sheet.GetRow(row);
if (xlRow == null)
{
break;
}
var cell = xlRow.GetCell(col);
row++;
if (cell == null)
{
continue;
}
cell.Hyperlink = null;
}
using (var fs = File.OpenWrite(Excel))
{
hssfWorkbook.Write(fs);
}
return(new ResultadoOperacion()
{
Ok = true
});
}
/// <summary>
/// Method for getting the data by Row
/// </summary>
/// <params>SheetName,RowNumber,String FilePath=null </params>
/// <return>String array</returns>
public string[] GetRowDataFromExcel(string sheetName, int rowNum, string filePath = null)
{
try
{
if (filePath == null)
{
filePath = getPath();
}
log.Info(filePath);
List <string> al = new List <string>();
string data;
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
file.Close();
ISheet sheet = hssfworkbook.GetSheet(sheetName);
IRow r = sheet.GetRow(rowNum);
for (int i = 0; i < r.LastCellNum; i++)
{
data = r.GetCell(i).ToString();
al.Add(data);
}
return(al.ToArray());
}
catch (FileNotFoundException)
{
throw new FileNotFoundException();
}
catch (NullReferenceException)
{
throw new NullReferenceException();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Excel文件导成Datatable
/// </summary>
/// <param name="ms">Excel文件流</param>
/// <param name="sheetname">Excel表名</param>
/// <returns></returns>
public static DataTable EecelToDataTable(Stream ms, string sheetname)
{
DataTable dt = new DataTable();
HSSFWorkbook workbook = new HSSFWorkbook(ms);
var sheet = workbook.GetSheet(sheetname);
if (null == sheet)
{
return(null);
}
//添加列
var row = sheet.GetRow(0);
var val = "";
for (int i = 0; i < row.LastCellNum; i++)
{
val = row.GetCell(i).StringCellValue;
if (string.IsNullOrEmpty(val) || dt.Columns.Contains(val))
{
dt.Columns.Add(string.Format("第{0}列{1}", i + 1, val));
}
else
{
dt.Columns.Add(val);
}
}
//数据
for (int ri = 1; ri < sheet.LastRowNum; ri++)
{
var dr = dt.NewRow();
row = sheet.GetRow(ri);
for (int i = 0; i < dt.Columns.Count && i < row.LastCellNum; i++)
{
dr[i] = row.GetCell(i).StringCellValue;
}
dt.Rows.Add(dr);
}
return(dt);
}
public void GetGroups()
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook book = new HSSFWorkbook(fs);
int count = book.NumberOfSheets;
Year year = new Year();
Context.Years.Add(year);
if (year.Groups == null)
{
year.Groups = new List <Models.Group>();
}
for (int a = 0; a < count; a++)
{
string nameOfGroup = book.GetSheetName(a);
Regex regex = new Regex("^\\d");
if (regex.IsMatch(nameOfGroup))
{
Models.Group group = new Models.Group()
{
NameGroup = nameOfGroup
};
year.Groups.Add(group);
}
}
GetStudents(year, book);
ISheet sheet = book.GetSheet(year.Groups[0].NameGroup);
string yearStr = sheet.GetRow(1).GetCell(1).StringCellValue;
Regex regexYear = new Regex("\\d{4}\\/\\d{4}");
year.Name = regexYear.Match(yearStr).Value;
FilterSubjects();
book.Close();
}
Context.SaveChanges();
}
/// <summary>
/// 根据表名获取表
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath, string sheetName)
{
string outMsg = "";
var dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
try
{
ISheet sheet = null;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
//2007版
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out outMsg);
}
}
else if (fileType == ".xls")
{
//2003版
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out outMsg);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(dt);
}
public void xpto()
{
// Open Template
FileStream fs = new FileStream("c:\\lixo\\Extrato.xlt", FileMode.Open, FileAccess.Read);
// Load the template into a NPOI workbook
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
// Load the sheet you are going to use as a template into NPOI
NPOI.SS.UserModel.ISheet asheet = templateWorkbook.GetSheet("SaldoProjetos");
// Insert data into template
asheet.GetRow(10).GetCell(3).SetCellValue(100); // Inserting a string value into Excel
// Save the NPOI workbook into a memory stream to be sent to the browser, could have saved to disk.
MemoryStream ms = new MemoryStream();
templateWorkbook.Write(ms);
// Send the memory stream to the browser
// ExportDataTableToExcel(ms, "c:\\lixo\\EventExpenseReport.xls");
}
private static void PreencherInformacoesDespesas(ICollection <Despesa> despesas)
{
ISheet sheetCatalogo = _documento.GetSheet("Despesas");
int numeroProximaLinha = 3;
foreach (Despesa despesa in despesas)
{
sheetCatalogo.GetCell(numeroProximaLinha, 1).SetCellValue(despesa.Data.ToShortDateString());
sheetCatalogo.GetCell(numeroProximaLinha, 2).SetCellValue(despesa.PegarTipo());
sheetCatalogo.GetCell(numeroProximaLinha, 3).SetCellValue(despesa.Fornecedor.Nome);
sheetCatalogo.GetCell(numeroProximaLinha, 4).SetCellValue(despesa.Item.Descricao);
sheetCatalogo.GetCell(numeroProximaLinha, 5).SetCellValue(despesa.Quantidade);
sheetCatalogo.GetCell(numeroProximaLinha, 6).SetCellValue(despesa.ValorUnitario);
sheetCatalogo.GetCell(numeroProximaLinha, 7).SetCellValue(despesa.ValorTotal);
if (despesa.Animal != null)
{
sheetCatalogo.GetCell(numeroProximaLinha, 8).SetCellValue(despesa.Animal.Especie);
}
numeroProximaLinha++;
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
string strSampleId = this.strSampleId.Value;
//获取基本信息
DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleId, "03", "0");
//修改打印状态
new TMisMonitorSampleInfoLogic().updateSamplingAllocationSheetInfoStatus(strSampleId, "03", "1");
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/SamplingAllocationSheet.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
sheet.GetRow(2).GetCell(0).SetCellValue("交接日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.GetRow(i + 4).GetCell(0).SetCellValue((dt.Rows[i]["SAMPLE_CODE"].ToString()));
sheet.GetRow(i + 4).GetCell(2).SetCellValue(dt.Rows[i]["SAMPLE_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(3).SetCellValue(dt.Rows[i]["SAMPLE_COUNT"].ToString());
sheet.GetRow(i + 4).GetCell(5).SetCellValue(dt.Rows[i]["ITEM_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(6).SetCellValue(dt.Rows[i]["IS_OK"].ToString());
sheet.GetRow(i + 4).GetCell(7).SetCellValue(dt.Rows[i]["SAMPLING_MANAGER_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(8).SetCellValue(dt.Rows[i]["SAMPLE_ACCESS_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(9).SetCellValue(dt.Rows[i]["SAMPLE_ACCESS_DATE"].ToString() == "" ? "" : DateTime.Parse(dt.Rows[i]["SAMPLE_ACCESS_DATE"].ToString()).ToString("yyyy-MM-dd"));
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("样品交接记录表.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
{
var sheetName = "_constraintSheet_";
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = "";
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果只有一个可选值，则增加一个相同的选项
if (values.Count() == 1)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(lastValue);
rowIndex++;
}
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), true);
return(new HSSFDataValidation(cellRegions, constraint));
}
public void Reader()
{
DataTable table = new DataTable("book"); //创建表数据容器
table.Columns.Add(new DataColumn("name1", typeof(string))); //初始化列
table.Columns.Add(new DataColumn("name2", typeof(string)));
table.Columns.Add(new DataColumn("id", typeof(string)));
DataRow dr = null; //声明DataRow行
FileStream stream = new FileStream(filename, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(stream); // 读取Excel数据
ISheet sheet = wb.GetSheet("sheet1"); //指定Sheet
for (int i = 1; i < sheet.LastRowNum; i++) //遍历Excel的行数，由于该表第一行为标题（0行）需要从1开始
{
IRow row = sheet.GetRow(i); //获取Execl行数据
dr = table.NewRow(); //创建DataRow
for (int j = 0; j < row.Cells.Count; j++) //遍历Excel的列数
{
string value = row.GetCell(j).ToString(); //获取指定行指定列的数据
dr[j] = value; //给DataRow赋值
}
table.Rows.Add(dr); // 将DataRow数据添加到DataTable数据表中
}
/**
* 遍历DataTalbe
*/
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
string strName1 = table.Rows[i][j].ToString();
Console.WriteLine(strName1);
}
}
Console.ReadKey();
}
/// <summary>
/// WEBAPI 下载Excel表格文件
/// </summary>
/// <param name="request"></param>
/// <param name="dt"></param>
/// <param name="filename"></param>
/// <returns></returns>
//public static HttpResponseMessage DownLoadExcel(this HttpRequestMessage request, DataTable dt, String filename)
//{
// var response = request.CreateResponse(HttpStatusCode.OK);
// HSSFWorkbook book = new HSSFWorkbook();
// ISheet sheet = book.CreateSheet(dt.TableName);
// IRow row = sheet.CreateRow(0);
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
// }
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// IRow row2 = sheet.CreateRow(i + 1);
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
// }
// }
// // 写入到客户端
// System.IO.MemoryStream stream = new System.IO.MemoryStream(8192);
// book.Write(stream);
// stream.Seek(0, SeekOrigin.Begin);
// response.Content = new StreamContent(stream);
// response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
// response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
// {
// FileName = filename
// };
// book = null;
// return response;
//}
/// <summary>
/// 从 Xls文件导入数据到 DataTable
/// </summary>
/// <param name="filePath"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public static DataTable Read(string filePath, String SheetName)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheet(SheetName);
if (sheet == null)
{
return(new DataTable());
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable(SheetName);
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
ICell cell = sheet.GetRow(0).Cells[j];
dt.Columns.Add(cell.ToString());
}
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow Row = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
Row[i] = cell == null ? null : cell.ToString();
}
dt.Rows.Add(Row);
}
hssfworkbook.Close();
hssfworkbook = null;
return(dt);
}
//机票会计总账(DepCode=订单号开头2个数字21,22,23,25)
private void TicketAccount(int Year, int Month, int DepCode)
{
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "Invoice\\Ticket_Account_Template.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1");
DataTable dt;
int[] arrDep = { 21, 22, 23, 25 };
int startRow = 0;
if (DepCode == 0)
{
foreach (int iDep in arrDep)
{
dt = Ticket.GetTicketAccountingReport(Year, Month, iDep);
FillExcelAccount(sheet1, dt, startRow);
startRow += dt.Rows.Count + 1;
}
}
else
{
dt = Ticket.GetTicketAccountingReport(Year, Month, DepCode);
FillExcelAccount(sheet1, dt, startRow);
}
//Excel文件在被打开的时候自动将焦点定位在单元格
sheet1.GetRow(0).GetCell(0).SetAsActiveCell();
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
string FullFileName = AppDomain.CurrentDomain.BaseDirectory + "Upload\\Excel\\Ticket_Account_" + Year.ToString() + Month.ToString("00") + ".xls";
file = new FileStream(FullFileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
DownloadFileAsAttachment(FullFileName);
}
public void TestBug43093()
{
HSSFWorkbook xlw = new HSSFWorkbook();
AddNewSheetWithCellsA1toD4(xlw, 1);
AddNewSheetWithCellsA1toD4(xlw, 2);
AddNewSheetWithCellsA1toD4(xlw, 3);
AddNewSheetWithCellsA1toD4(xlw, 4);
NPOI.SS.UserModel.Sheet s2 = xlw.GetSheet("s2");
Row s2r3 = s2.GetRow(3);
Cell s2E4 = s2r3.CreateCell(4);
s2E4.CellFormula = ("SUM(s3!B2:C3)");
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(xlw);
double d = eva.Evaluate(s2E4).NumberValue;
// internalEvaluate(...) Area3DEval.: 311+312+321+322 expected
Assert.AreEqual(d, (311 + 312 + 321 + 322), 0.0000001);
// System.out.println("Area3DEval ok.: 311+312+321+322=" + d);
}
/// <summary>
/// 由DataTable导出Excel(适应于基本的模版导出，且不超过65535条)
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="modelpath">模版文件实体路径</param>
/// <param name="modelName">模版文件名称</param>
/// <param name="fileName">指定Excel工作表名称</param>
/// <param name="sheetName">作为模型的Excel</param>
/// <param name="rowindex">从第几行开始写入数据(此为行索引，若为1则从第2行开始写入数据)</param>
/// <returns>Excel工作表</returns>
public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName, int rowIndex)
{
int colIndex = 0;
FileStream file = new FileStream(modelpath + "/" + modelName, FileMode.Open, FileAccess.Read);//读入excel模板
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName);
if (sourceTable.Rows.Count + rowIndex > 65535)
{
throw new ArgumentException("数据太多，系统尚不支持，请缩小查询范围!");
}
foreach (DataRow row in sourceTable.Rows)
{ //双循环写入sourceTable中的数据
colIndex = 0;
HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);
foreach (DataColumn col in sourceTable.Columns)
{
xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
colIndex++;
}
rowIndex++;
}
sheet1.ForceFormulaRecalculation = true;
//CS项目适用胡方法
//FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存
//hssfworkbook.Write(fileS);
//fileS.Close();
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
public void TestCaseInsensitiveNames()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet originalSheet = b.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet fetchedSheet = b.GetSheet("sheet1");
if (fetchedSheet == null)
{
throw new AssertionException("Identified bug 44892");
}
Assert.AreEqual(originalSheet, fetchedSheet);
try
{
b.CreateSheet("sHeeT1");
Assert.Fail("should have thrown exceptiuon due to duplicate sheet name");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("The workbook already contains a sheet of this name", e.Message);
}
}
public static string CreateAmazonFeedInventoryFile(List <MarketplaceInventoryUpdateItem> inventoryFeeds)
{
//var template = @"D:\dev\kigwa\EShopo System\EIS.Inventory\EIS.Consoles\EIS.SchedulerTaskApp\Templates\Flat.File.PriceInventory.xls";
var template = string.Format("{0}\\Flat.File.PriceInventory.xls", ConfigurationManager.AppSettings["TemplatesRoot"]);
var newFile = string.Format("{0}\\AmazonInventoryFeed-{1:yyyyMMdd_HHmmss}.xls",
ConfigurationManager.AppSettings["MarketplaceFeedRoot"],
DateTime.Now);
HSSFWorkbook workbook;
using (var fs = new FileStream(template, FileMode.Open, FileAccess.Read))
{
// Getting the complete workbook...
workbook = new HSSFWorkbook(fs);
fs.Close();
}
// Getting the worksheet by its name...
var sheet = workbook.GetSheet("Price Template");
for (var index = 1; index <= inventoryFeeds.Count; index++)
{
var row = sheet.CreateRow(index);
row.CreateCell(0).SetCellValue(inventoryFeeds[index - 1].SKU);
//row.CreateCell(4).SetCellValue(inventoryFeeds[index - 1].InventoryQuantity);
row.CreateCell(4).SetCellValue(101);
}
// Writing the workbook content to the FileStream...
using (var fileStream = new FileStream(newFile, FileMode.Create))
{
workbook.Write(fileStream);
fileStream.Flush();
fileStream.Close();
}
return(newFile);
}
private void отчетToolStripMenuItem_Click(object sender, EventArgs e)
{
if (activeTable == 4)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.DefaultExt = ".xls";
dialog.Filter = "Таблицы Excel (*.xls)|*.xls|Все файлы (*.*)|*.*";
dialog.FilterIndex = 1;
dialog.FileName = "Отчет";
if (dialog.ShowDialog() == DialogResult.OK)
{
var file = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite);
var query = (from p in db.payment
join u in db.users on p.id_s equals u.id_u
orderby p.id_p
select new { p.id_p, u.surname, u.name, u.patron, p.summa, p.pay_day }).ToList();
var template = new MemoryStream(Properties.Resources.template, true);
HSSFWorkbook workbook = new HSSFWorkbook(template);
var sheet1 = workbook.GetSheet("Лист1");
sheet1.CreateRow(3).CreateCell(0).SetCellValue(DateTime.Now.ToShortDateString());
int row = 6;
foreach (var item in query.OrderBy(o => o.id_p))
{
var rowInsert = sheet1.CreateRow(row);
rowInsert.CreateCell(0).SetCellValue(item.id_p);
rowInsert.CreateCell(1).SetCellValue(item.surname);
rowInsert.CreateCell(2).SetCellValue(item.name);
rowInsert.CreateCell(3).SetCellValue(item.patron);
rowInsert.CreateCell(4).SetCellValue(item.summa);
rowInsert.CreateCell(5).SetCellValue(item.pay_day);
row++;
}
workbook.Write(file);
}
}
}
