| 319 | @router.post("/uploadExcel", response_model=None, summary=f"{PLACEHOLDER_PREFIX}ds_upload_excel") |
| 320 | @require_permissions(permission=SqlbotPermission(role=['ws_admin'])) |
| 321 | async def upload_excel(session: SessionDep, file: UploadFile = File(..., description=f"{PLACEHOLDER_PREFIX}ds_excel")): |
| 322 | ALLOWED_EXTENSIONS = {"xlsx", "xls", "csv"} |
| 323 | if not file.filename.lower().endswith(tuple(ALLOWED_EXTENSIONS)): |
| 324 | raise HTTPException(400, "Only support .xlsx/.xls/.csv") |
| 325 | |
| 326 | os.makedirs(path, exist_ok=True) |
| 327 | filename = f"{file.filename.split('.')[0]}_{hashlib.sha256(uuid.uuid4().bytes).hexdigest()[:10]}.{file.filename.split('.')[1]}" |
| 328 | save_path = os.path.join(path, filename) |
| 329 | with open(save_path, "wb") as f: |
| 330 | f.write(await file.read()) |
| 331 | |
| 332 | def inner(): |
| 333 | sheets = [] |
| 334 | engine = get_engine_conn() |
| 335 | if filename.endswith(".csv"): |
| 336 | df = pd.read_csv(save_path, engine='c') |
| 337 | tableName = f"sheet1_{hashlib.sha256(uuid.uuid4().bytes).hexdigest()[:10]}" |
| 338 | sheets.append({"tableName": tableName, "tableComment": ""}) |
| 339 | insert_pg(df, tableName, engine) |
| 340 | else: |
| 341 | sheet_names = pd.ExcelFile(save_path).sheet_names |
| 342 | for sheet_name in sheet_names: |
| 343 | tableName = f"{sheet_name}_{hashlib.sha256(uuid.uuid4().bytes).hexdigest()[:10]}" |
| 344 | sheets.append({"tableName": tableName, "tableComment": ""}) |
| 345 | # df_temp = pd.read_excel(save_path, nrows=5) |
| 346 | # non_empty_cols = df_temp.columns[df_temp.notna().any()].tolist() |
| 347 | df = pd.read_excel(save_path, sheet_name=sheet_name, engine='calamine') |
| 348 | insert_pg(df, tableName, engine) |
| 349 | |
| 350 | # os.remove(save_path) |
| 351 | return {"filename": filename, "sheets": sheets} |
| 352 | |
| 353 | return await asyncio.to_thread(inner) |
| 354 | |
| 355 | |
| 356 | def insert_pg(df, tableName, engine): |