---
# System prepended metadata

title: React直接接資料庫

---

# React直接接資料庫
故事是這樣的:
榮總醫院急診的專案，當初說只是畫面翻新而已，所以只有前端，結果到後面就變成越來越複雜，阿不就還好我用django寫過後端，大概知道流程後，就自己嘗試用js寫了。

急診系統裡有一個東西叫做"模板"，讓醫生可以直接一鍵填寫的功能。

客戶給了一個excel，說這是最新版的模板

???所以勒我要幹嘛??

事情演變到最後就是我要用後端去excel中找東西，回傳到前端
# 把excel當資料庫
先匯入excel處理包
> FormType 要先寫好每個excel欄位對應的type
:::spoiler
excel的欄位中會有XX1,XX2這種，但我顯示在畫面上只有要一個XX，也就是我會需要彙整XX1與XX2
```javascript=
export interface FormType {
  id:string;
  ERSBKEY?: string;
  名稱?: string;
  科別?:string;

  CHIEFCOMPLAINT:string|""; // 彙整
  CHIEFCOMPLAINT1?: string;
  CHIEFCOMPLAINT2?: string;
  CHIEFCOMPLAINT3?: string;

  PRESENTILLNESS?: string|"";
  PRESENTILLNESS1?: string;
  PRESENTILLNESS2?: string;
  PRESENTILLNESS3?: string;
  PRESENTILLNESS4?: string;

  PASTHISTORY?: string;
  PASTHISTORY1?: string;
  PASTHISTORY2?: string;
  PASTHISTORY3?: string;

  GENERALCONDITION?: string;
  GENERALCONDITION1?: string;
  GENERALCONDITION2?: string;

  HEENT?: string;
  NECK?: string;

  HESTAndLUNGS?: string;
  HESTAndLUNGS1?: string;
  HESTAndLUNGS2?: string;

  ABDOMEN?: string,
  ABDOMEN1?: string;
  ABDOMEN2?: string;

  BACKAndSPINE?: string;
  EXOGENITALIA?: string;
  RECTALEXAM?: string;
  EXTREMITIES?: string;
  NEUROLOGICALEXAM?: string;
}
```
:::
```javascript=
import * as XLSX from 'xlsx';
import { FormType } from '@/lib/data/data';

type Props = {
  closeModal: () => void;
  tab: string; // 會根據這個tab來搜尋
  onSelect: (item:FormType ) => void; // excel中的東西
};

export default function TraumaModal({ tab, onSelect,closeModal }: Props) {
    
    const handleBackdropClick = (e: MouseEvent<HTMLDivElement>) => {
        if (e.target === e.currentTarget) {
          closeModal();
        }
     };
    
    const [data, setData] = useState<FormType[]>([]); // 接收api回傳的資料
```
## 主要部分
```javascript=
useEffect(() => {
  fetch('/急診病歷既有模組.xlsx')
    .then((res) => res.arrayBuffer()) 
    .then((buffer) => {
      const workbook = XLSX.read(buffer, { type: 'array' });
      const sheet = workbook.Sheets[workbook.SheetNames[0]];
      const json = XLSX.utils.sheet_to_json<FormType>(sheet);
      setData(json);  // 直接賦值即可
    });
}, []);

const filtered = data.filter((d) => d['科別' ]?.includes(tab));
```
將excel對應頁面表格
```javascript=
 const handleClickItem = (item: FormType) => {
    onSelect({
      ERSBKEY: item['ERSBKEY'] || '',
      名稱: item['名稱'] || '',

      CHIEFCOMPLAINT: [item['CHIEFCOMPLAINT1'], item['CHIEFCOMPLAINT2'], item['CHIEFCOMPLAINT3']].filter(s => s?.trim()).join('\n'),

      PRESENTILLNESS: [item['PRESENTILLNESS1'], item['PRESENTILLNESS2'], item['PRESENTILLNESS3'], item['PRESENTILLNESS4']].filter(s => s?.trim()).join('\n'),

      PASTHISTORY: [item['PASTHISTORY1'], item['PASTHISTORY2'], item['PASTHISTORY3']].filter(s => s?.trim()).join('\n'),

      GENERALCONDITION: [item['GENERALCONDITION1'], item['GENERALCONDITION2']].filter(s => s?.trim()).join('\n'),

      HESTAndLUNGS: [item['HESTAndLUNGS1'], item['HESTAndLUNGS2']].filter(s => s?.trim()).join('\n'),

      ABDOMEN: [item['ABDOMEN1'], item['ABDOMEN2']].filter(s => s?.trim()).join('\n'),


      HEENT: item['HEENT'] || '',
      NECK: item['NECK'] || '',
      BACKAndSPINE: item['BACKAndSPINE'] || '',
      EXOGENITALIA: item['EXOGENITALIA'] || '',
      RECTALEXAM: item['RECTALEXAM'] || '',
      EXTREMITIES: item['EXTREMITIES'] || '',
      NEUROLOGICALEXAM: item['NEUROLOGICALEXAM'] || '',
      id: item['id'] || '',
    });
    closeModal();
  };
```
## 主畫面
```html=
 return (
  <div onClick={handleBackdropClick}>
     <div className={styles.modalTitle}>{tab}科 模組</div>
     <button onClick={closeModal}>✕</button>
     <div>
        {filtered.map((item, idx) => (
             <div
            key={idx}
            onClick={() => handleClickItem(item)}
              >
                🩺{item['名稱']}
              </div>
            ))}
    </div>
</div>
```
# 用api.js接資料庫
[如何將excel匯入MySQL](https://hackmd.io/U4pftBjARg6uttniGbbtsg#%E5%B0%87excel%E5%8C%AF%E5%85%A5mySQL)