private void ToolStripButton_Export_Click(object sender, EventArgs e)
{
var template = GetSelectTemplate();
if (template == null)
{
return;
}
SaveFileDialog fileDialog = new SaveFileDialog();
//设置文件类型
fileDialog.Filter = "Excel文档(*.xls)|*.xls";
//设置默认文件类型显示顺序
fileDialog.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
fileDialog.RestoreDirectory = true;
//设置默认文件名
fileDialog.FileName = template.TemplateName;
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filePath = Path.GetDirectoryName(fileDialog.FileName);
string extension = Path.GetExtension(fileDialog.FileName);
var tempStocks = _spotDataSource.Where(p => p.TemplateNo == template.TemplateId).ToList();
List <DataHeader> cellRanges = null;
var table = GridToExecl(tempStocks, out cellRanges);
ExcelUtil.CreateExcel(fileDialog.FileName, table, cellRanges);
}
}
static void Main(string[] args)
{
BookWrapper wrapper = ExcelUtil.CreateExcel();
SheetWrapper swrapper = wrapper.CreateSheet("Test");
CellPos pos = CellPos.CreateCellPos(0, 0);
swrapper.SetText(pos, "Test");
Console.WriteLine("Cell: " + swrapper.GetText(pos));
Console.WriteLine("Cell Empty: " + swrapper.GetText(CellPos.CreateCellPos(1, 0)));
sbyte[] t = wrapper.GetBytes();
wrapper.Save("c:\\temp\\test.xlsx");
BookWrapper w2 = ExcelUtil.LoadExcel(t);
SheetWrapper s2 = w2["Test"];
Console.WriteLine(s2.GetText(CellPos.CreateCellPos(0, 0)));
}
public void TestExcelRead_cellRange()
{
var sheetConfig = ConfigManager.Instance.GetImportConfig().GetSheet("stocktemplate");
var cellRanges = ConfigManager.Instance.GetImportConfig().GetColumnHeader(sheetConfig.Columns);
Dictionary <string, DataColumnHeader> colHeadMap = new Dictionary <string, DataColumnHeader>();
foreach (var column in cellRanges)
{
colHeadMap.Add(column.Name, column);
}
string fileName = @"d:/temp/20160222saveas.xls";
string sheetName = @"20160222";
var table = ExcelUtil.GetSheetData(fileName, sheetName, colHeadMap);
Console.WriteLine("test");
string newFileName = @"d:/temp/20160222_newcopy_cellRange.xls";
ExcelUtil.CreateExcel(newFileName, table, sheetConfig.Columns);
Console.WriteLine("test2");
}
public BaseOutput UploadExcelFile(List <IFormFile> files, string priceSheetName)
{
if (files == null || files.Count == 0)
{
return(Fail("未上传文件"));
}
if (files.Count != 1)
{
return(Fail("一次只能上传一个文件"));
}
var formFile = files[0];
if (formFile.Length == 0)
{
return(Fail("文件不能为空"));
}
var now = DateTime.Now;
var directory = Path.Combine(Directory.GetCurrentDirectory(), "files", now.ToString("yyyy"), now.ToString("MM"), now.ToString("dd"));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
var fileName = $"{MD5.Encrypt(formFile.OpenReadStream())}-{formFile.FileName}";
if (!System.IO.File.Exists(Path.Combine(directory, fileName)))
{
var extension = Path.GetExtension(formFile.FileName);
var workBook = ExcelUtil.GetWorkbook(extension, formFile.OpenReadStream());
var dic = ExcelUtil.ImportExcelFile(workBook, priceSheetName);
if (dic.Count == 0)
{
return(Fail("价格列表为空"));
}
if (dic.Values.Any(f => !Regex.IsMatch(StringUtil.RemoveSpace(f), @"\d+(\.\d+)?")))
{
return(Fail("价格不格式不正确"));
}
var mapList = ExcelUtil.GetMap <Darunfa>();
var headerRow = ExcelUtil.FindHeaderRow(workBook, mapList); //寻找标题行
var list = ExcelUtil.ImportExcelFile <Darunfa>(headerRow, mapList);
foreach (var item in list)
{
if (!string.IsNullOrEmpty(item.Request) && dic.ContainsKey(item.Request))
{
item.Price = dic[item.Request];
item.Price = string.IsNullOrWhiteSpace(item.Price) ? "0" : Regex.Replace(item.Price, @"\s+", string.Empty);
if (!string.IsNullOrWhiteSpace(item.Size))
{
item.Size = Regex.Replace(item.Size, @"\s+", string.Empty);
if (Regex.IsMatch(item.Size, @"\d+(\.\d+)?\*\d+(\.\d+)?"))
{
var size = Regex.Split(item.Size, @"\*+");
var total = Convert.ToDecimal(size[0]) * Convert.ToDecimal(size[1]) * Convert.ToDecimal(item.Num) * Convert.ToDecimal(item.Price);
total = decimal.Round(total, 2);
item.Total = (total == Convert.ToInt32(total) ? Convert.ToInt32(total) : total).ToString();
}
}
}
}
var data = ExcelUtil.CreateExcel(extension, list, mapList, headerRow);
if (!System.IO.File.Exists(Path.Combine(directory, fileName)))
{
System.IO.File.WriteAllBytes(Path.Combine(directory, fileName), data);
}
}
var output = new BaseOutput <string>();
output.Data = $"/files/{now.ToString("yyyy")}/{now.ToString("MM")}/{now.ToString("dd")}/{fileName}";
return(output);
}